Teradata Comparison Operators and Performance: Why Choosing the Right Operator Matters

The performance of Teradata is somewhat dependent on its comparison operators.

Selecting the appropriate comparison operator is crucial in distinguishing between efficient and inefficient queries.

How do the chosen comparison operators affect performance?

Quality statistics are a crucial input for the Teradata Optimizer, significantly influencing the execution plan’s aggressiveness or conservatism.

Teradata stores column-level statistics, which can be used for substrings of a column despite the inability to collect statistics at a finer granularity. Teradata 14.10 offers expanded capabilities for utilizing available statistics compared to previous versions.

Consider a SQL query featuring a WHERE clause targeting a designated column. Supposing a code is stored in a character column titled “ORIGIN_CD,” our objective is to gather all entries where the column begins with the letter ‘A.’

There are various ways to execute this SQL statement, and each option may yield a unique execution plan with distinct access paths, runtimes, and resource usage patterns.

SELECT * FROM <TABLE> WHERE ORIGIN_CD LIKE ‘A%’;

SELECT * FROM <TABLE> WHERE SUBSTR(ORIGIN_CD,1,1) = ‘A’;

In both cases, applying a function to the “ORIGIN_CD” column may hinder the Optimizer from utilizing column statistics, depending on your Teradata version. Avoiding functions on columns present in a WHERE or JOIN condition is recommended. Using functions on join columns could indicate a poor design or an outdated data model.

Conclusion:

Explore alternative solutions to enhance performance when applying functions to WHERE and JOIN conditions.