Teradata Comparison Operators and Performance are interrelated to some extent.
Choosing the proper comparison operator can differentiate between well-performing and low-performing queries.
But why should the selected comparison operators have an impact on performance?
Statistics are an essential input to the Teradata Optimizer, and they have a considerable quality impact on the execution plan and how aggressive or conservative it will be.
Teradata stores statistics on the column level. While it is impossible to collect statistics on a more detailed level, this does not mean that column statistics can’t be used for a substring of a column. Teradata 14.10 has many more possibilities to use available statistics than earlier versions.
Here is one example:
Imagine a SQL statement with a WHERE condition on a specified column. Let’s assume a code stored in a character column named “ORIGIN_CD,” We would like to retrieve all rows where the column starts with an ‘A.’
You have several options to implement this SQL statement; each one may result in a different execution plan with a different access path, runtime, and resource usage characteristics:
SELECT * FROM <TABLE> WHERE ORIGIN_CD LIKE ‘A%’;
SELECT * FROM <TABLE> WHERE SUBSTR(ORIGIN_CD,1,1) = ‘A’;
Both examples apply a function on top of the column “ORIGIN_CD.” Depending on your version of Teradata, these functions may be preventing the Optimizer from using column statistics. Avoid any function on a WHERE or JOIN condition column. Functions applied to join columns point to a bad design or a defunct data model.
If you can’t avoid applying functions on WHERE and JOIN conditions, try different solutions as they might significantly improve performance.