Teradata Comparison Operators and Performance

Roland Wenzlofsky

March 11, 2014

minutes reading time


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.

Conclusion:

If you can’t avoid applying functions on WHERE and JOIN conditions, try different solutions as they might significantly improve performance.

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

You might also like

>