Teradata Comparison Operators and Performance | DWHPRO

Teradata Comparison Operators and Performance

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 poor performing query.

But why should the selected comparison operators have an impact on performance?

Statistics are the most important input to the Teradata Optimizer, and they have a huge quality impact on the execution plan and how aggressive or conservative it will be.

Teradata stores statistics on 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 much 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 different access path, runtime and resource usage characteristics:



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 on join columns point to a bad design or a defunct data model.


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.

Roland Wenzlofsky

Roland Wenzlofsky is a graduated computer scientist and Data Warehouse professional working with the Teradata database system for more than 20 years. He is experienced in the fields of banking and telecommunication with a strong focus on performance optimization.