DWHPro Insights

Teradata 16 Introduces New Features for UNION ALL Queries

By Roland Wenzlofsky ·

Teradata 16 Introduces New Features for UNION ALL Queries

Teradata UNION ALL – The New Features of Release 16

Teradata 16 introduces new features for UNION ALL queries that can enhance their performance, although their application is not guaranteed.

The Optimizer will opt for executing the query through conventional methods if the cost estimations for the traditional execution plan are lower. It is crucial to provide the Optimizer with accurate statistics.

Prior to Teradata 16, the execution plan for a join involving a derived table or view that includes a UNION ALL operation appeared as follows:

  • Create a common spool from all branches of the UNION ALL
  • Do the usual join preparation steps (rehashing, duplicating, etc.) for this spool.
  • Join the spool (left table) with the right table

These historical release descriptions do not establish a universal predicate/aggregation-pushdown boundary. Confirm exact release rules and inspect the actual plan.

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

Beginning with Teradata 16, the Optimizer can execute queries featuring UNION ALL differently:

  • Join spool of first UNION ALL branch (left table) against the right table
  • Join spool of second UNION ALL branch (left table) against the right table
  • Join spool of last UNION ALL branch (left table) against the right table
  • Merge the join results into a common spool

Smaller partial spools can help, but repeated branch joins can add cost. Compare total CPU, I/O, spool and elapsed time; branch-local execution is a cost-based choice, not a guaranteed improvement.

SELECT COUNT(*) queries containing UNION ALL now have an additional enhancement.

The Optimizer can efficiently calculate the number of rows for each branch by utilizing the cylinder index technique, which is the same method used for single tables, instead of consolidating all branches into a common spool and manually counting each row. The Optimizer then sums up the results.

Unfortunately, the aforementioned improvements are incompatible with UNION queries. Please share your thoughts in the comments.

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.

2 thoughts on “Teradata 16 Introduces New Features for UNION ALL Queries”

  1. Table1:
    CLIENTNAME
    Roland

    Table2:
    CLIENTNAME
    Roland
    Jiri
    Patrick

    UNION ALL: will return 1 row from Table1 + 3 rows from Table2 = 4 rows. This is deterministic, and every table separate will always return the same number of rows, so here you can push the calculations down to the branches.
    UNION: as explained by Jiri, will remove duplicates. So only looking to table 2, you don’t know how many rows will be returned. You need to know the context from the other branches.

    Reply
  2. I think the answer is: UNION eliminates duplicate rows. There’s no way of telling whether there’re going to be any duplicates in the UNIONed set just by looking to Cylinder index.

    🙂

    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