Teradata UNION ALL Enhancements


Teradata UNION ALLTeradata UNION ALL – The New Features of Release 16

Teradata 16 introduces several new features for queries containing UNION ALL. The usage of these new features can help to improve query performance, but will not always be applied.

The optimizer will decide to execute the query without making use of these features if cost estimations for the traditional execution plan are lower. Like always, it’s important to support the optimizer with correct statistics.

Before Teradata 16, the execution plan for a join between a derived table or view containing UNION ALL looks like this:

  • 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

Until Teradata 15.10, the optimizer will not apply any reduction (WHERE conditions, etc.) before all branches of the statement containing UNION ALL are placed in a common spool.

Starting with Teradata 16, the optimizer has the possibility to execute queries containing UNION ALL in a different way:

  • 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

This approach will consume fewer resources than the original one if the partial join steps lead to smaller result spools for each partial join.

Likewise, aggregations can be applied on every single UNION ALL branch instead of spooling all branches into a common spool before doing the aggregation (which was the only available option for the optimizer until Teradata 15.10). Again, the performance improvement can be significant if the partial aggregations lead to small spool files.

Another enhancement was introduced for SELECT COUNT(*) queries which are containing UNION ALL.

Instead of moving all branches into a common spool and counting the number of rows one by one, the Optimizer can now use the cylinder index (the same technique used for single tables) to count the rows of each branch in a very efficient way and sums the result up afterward.

Unfortunately, these new enhancements cannot be used for UNION queries. Dou you know why? Leave your answer in the comment section.

If you have any questions about all this, please ask in the comments! I’ll be paying close attention and answering as many as I can. Thank you for reading. Whatever this blog has become, I owe it all to you.
Our Reader Score
[Total: 6    Average: 3.7/5]
Teradata UNION ALL Enhancements written by Roland Wenzlofsky on April 20, 2018 average rating 3.7/5 - 6 user ratings


  1. Table1:


    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.


Please enter your comment!
Please enter your name here