Teradata UNION ALL Enhancements

Roland Wenzlofsky

April 20, 2018

minutes reading time

Teradata 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 improve query performance but will not always be applied.

The Optimizer will decide to execute the query without using these features if cost estimations for the traditional execution plan are lower. As always, it’s essential 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 can 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 UNION ALL branch instead of spooling all branches into a common spool before 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 contain 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 calculate each branch’s rows very efficiently and sums the result up afterward.

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

  • Avatar
    Patrick Ericx says:



    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.

  • Avatar
    Jiří Hubáček says:

    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.


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

    You might also like