Teradata Comparison Operators and Performance are interrelated to some extent.
Choosing the proper comparison operator can make the difference between a well-performing and a low performing query.
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 not possible 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.
Her 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,” and 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.
Conclusion:
If you can’t avoid to apply functions on WHERE and JOIN conditions, try different solutions as they might bring a significant gain in performance.
