DWHPro Insights

Teradata Join Optimization: Early and Partial GROUP BY Techniques for Decision Support Workload

By Roland Wenzlofsky ·

Teradata Join Optimization: Early and Partial GROUP BY Techniques for Decision Support Workload

Teradata applies two very effective join optimization methods for decision support workloads, which usually involve a lot of aggregations:

Teradata GROUP BY-  Early and Partial Aggregation

Early and Partial GROUP BY are transformations that allow moving group operations in front of one or more join operations.

While Early GROUP BY executes the group statement, as a whole, before the join, Partial GROUP BY divides the aggregation, doing the first part of the aggregation before the join and the second part after.

This technique reduces resource usage for the join and the aggregation after the join (in the case of Early GROUP BY, no aggregation needs to be done after the join at all).

Teradata Query Performance Tuning: The DWHPro Guide book cover

Get the complete Teradata tuning book, free

Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis

206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.

Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.

About the free book

The Early Aggregation technique is used to reduce the number of rows as early as possible.

Whether the optimizer applies this technique depends on the estimated join costs.

Consider the following SQL query:

SELECT SUM(t01.a)
 FROM t01,t02,t03
 WHERE t01.b = t02.b AND t02.c = t03.c
 GROUP BY t01.d
 ;

This SQL exemplifies how the Teradata optimizer can partially group t01 by columns {t01.b, t01.d} by pushing the GROUP BY into relation t01.

The spool group can then be joined with relation t02. Finally, the resulting spool will be combined with t03, and a final GROUP BY will be applied.

Early and Partial GROUP BY, despite their complexity in execution plan creation, are commonly employed methods in modern database systems for data reduction. These approaches provide additional join options to the optimizer.

Partial grouping can be detected by looking at the EXPLAIN plan:

  • A partial SUM step is a clue, but inspect where it occurs relative to the join; its presence alone does not prove aggregation was pushed before that join.
  • Before the join takes place:  “we do a SORT/GROUP”

Collect statistics that improve estimates for the relevant joins and grouping keys, considering correlation and collection cost. It is not necessary to collect every column indiscriminately.

Need help with query performance or platform costs?

Diagnose plans, skew, spool pressure and expensive workloads. Agree a baseline and acceptance criteria before making changes.

Explore Teradata performance tuning

Teradata Query Performance Tuning: The DWHPro Guide book cover

Get the complete Teradata tuning book, free

Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis

206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.

Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.

About the free book

Follow DWHPro in Google to see our articles more often in Search.

1 thought on “Teradata Join Optimization: Early and Partial GROUP BY Techniques for Decision Support Workload”

  1. Thanks for explaining, how would early group by being classified and how could it be identified through explain plan.

    Reply

Leave a Comment

Teradata Query Performance Tuning: The DWHPro Guide book cover

Get the complete Teradata tuning book, free

Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis

206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.

Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.

About the free book

Protected by Security by CleanTalk and CleanTalk Anti-Spam