fbpx

Teradata Query Rewriting

By Roland Wenzlofsky

September 12, 2020


What is Teradata Query Rewriting?

Teradata query rewriting is part of the Teradata optimization process.

The optimizer replaces your query with a better version, which is assumed to be less resource-intense and, hopefully, faster.

Of course, both queries have to deliver the same result set.  To achieve better performance, Teradata utilizes several optimization strategies.

We will show you the most frequently applied optimization techniques.

1. Removing of unreferenced columns and expressions from the VIEW select list

Without this enhancement, all VIEW columns would have to be spooled during query execution.

Let’s assume we have the following view definition:

CREATE VIEW MyView AS
SELECT col1, col2, AVG(col3) myavg
FROM table1 t01, table2 t02
WHERE t01.pk=t02.pk GROUP BY 1,2;  

We are running the following query:

SELECT MAX(myavg) FROM MyView;

In the above example, view columns col1 and col2 can be eliminated, and only the column myavg will be spooled.

Another example will show you that it is even possible to eliminate spool usage:

SELECT COUNT(*) FROM MyView;  

The above query does not reference any view column. The number of rows can be derived from the cylinder index alone!

The elimination of view columns from the spool is a widespread technique the optimizer uses for spool usage reduction.

2. Conversion of outer joins to inner joins

Whenever the optimizer detects that a query can be satisfied with an inner join, the outer join will be replaced to improve join performance:

SELECT DISTINCT(col1)
FROM table1 LEFT OUTER JOIN table2 ON table1.pk=table2.pk
WHERE table2.col2 = 100 ;

In the above example, the outer join can safely be converted to an inner join as the WHERE condition filters any non-matching rows.

3. Folding of Views

When applying this enhancement, the optimizer completely removes the reference to views from the execution plan (this is called view folding).

Let’s assume we have the following view definition:

CREATE VIEW MyView AS
SELECT col1, col2, SUM(col3*col4) the_sum
FROM table1, table2
WHERE table1.pk=table2.pk GROUP BY 1,2;

We run the following query:

SELECT col2 FROM MyView WHERE the_sum > 100;

The optimizer would rewrite the above example in the following form:

SELECT col2 FROM table1, table2
WHERE table1.pk=table2.pk
GROUP BY 1,2
HAVING SUM(col3*col4) > 100;

As you can see, the view was eliminated from the execution plan.

4. Predicate Pushdown

With this technique, the optimizer pushes down existing query predicates. In the above example, the WHERE predicate would be applied already in the inner sub-query t01:

SELECT SUM(the_sum) the_overall_sum
FROM (
SELECT col1, col2, SUM(col3*col4) the_sum
FROM table1, table2
WHERE table1.col1=table2.col2
GROUP BY 1, 2 ) t01
WHERE col1 IN (1,2,3,4,5) ;

5. Elimination of SET operator branches

In case a SET branch contains an unsatisfiable condition,  the branch will be eliminated from the query.

Let’s assume we have the example query below:

SELECT * FROM table1 WHERE month_id= 1
UNION ALL
SELECT * FROM table2 WHERE month_id = 1 ;

Further, we assume to have a check constraint on month_id, ensuring that table1 only contains month_id = 1 and table2 contains month_id = 2:

The optimizer can safely rewrite the above query to:

SELECT * FROM table1 WHERE month_id = 1 ;

6. Elimination of Joins

Unneeded Joins are eliminated from the execution plan:

SELECT table1.* FROM table1 LEFT JOIN table2 ON table1.pk=table2.pk ;

The above query can be satisfied by spooling only table1:

SELECT table1.* FROM table1;

As you can see, the optimizer tries its best to improve your queries. I hope you enjoyed this post!

__CONFIG_colors_palette__{"active_palette":0,"config":{"colors":{"62516":{"name":"Main Accent","parent":-1}},"gradients":[]},"palettes":[{"name":"Default Palette","value":{"colors":{"62516":{"val":"var(--tcb-skin-color-0)"}},"gradients":[]},"original":{"colors":{"62516":{"val":"rgb(19, 114, 211)","hsl":{"h":210,"s":0.83,"l":0.45}}},"gradients":[]}}]}__CONFIG_colors_palette__
__CONFIG_colors_palette__{"active_palette":0,"config":{"colors":{"b4fbe":{"name":"Main Accent","parent":-1}},"gradients":[]},"palettes":[{"name":"Default Palette","value":{"colors":{"b4fbe":{"val":"rgb(241, 99, 52)"}},"gradients":[]},"original":{"colors":{"b4fbe":{"val":"rgb(19, 114, 211)","hsl":{"h":210,"s":0.83,"l":0.45}}},"gradients":[]}}]}__CONFIG_colors_palette__
Previous Article
__CONFIG_colors_palette__{"active_palette":0,"config":{"colors":{"b4fbe":{"name":"Main Accent","parent":-1}},"gradients":[]},"palettes":[{"name":"Default Palette","value":{"colors":{"b4fbe":{"val":"rgb(241, 99, 52)"}},"gradients":[]},"original":{"colors":{"b4fbe":{"val":"rgb(19, 114, 211)","hsl":{"h":210,"s":0.83,"l":0.45}}},"gradients":[]}}]}__CONFIG_colors_palette__
Next Article
Buy the Book Teradata Query Performance Tuning

Roland Wenzlofsky

Roland Wenzlofsky is an experienced freelance Teradata Consultant & Performance Trainer. Born in Austria's capital Vienna, he is building and tuning some of the largest Teradata Data Warehouses in the European financial and telecommunication sectors for more than 20 years. He has played all the roles of developer, designer, business analyst, and project manager. Therefore, he knows like no other the problems and obstacles that make many data warehouse projects fail and all the tricks and tips that will help you succeed.

  • Avatar
    Boris Mogilevsky says:

    Great article.
    Sounds that Optimizer became much better with Version 14.0.

  • Hi Roland:

    In what Teradata version did the ‘Query Rewrite of queries using views’ appear. I did not know that queries using views could be re-worked to eliminate the view-based-query entirely and go straight to the underlying tables – which support the view.

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

    You might also like

    >