March 11

0 comments

Teradata Comparison Operators and Performance

By Roland Wenzlofsky

March 11, 2014

comparision operator, sql

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:

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 on 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.

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.

You might also like

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

Never miss a good story!

 Subscribe to our newsletter to keep up with the latest trends!

>