Tag Archive

Tag Archives for " comparision operator "

Teradata Comparison Operators and Performance

teradata comparision operator

Updated 2015-07-07Teradata 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.

>