Teradata UNION ALL Enhancements
Teradata UNION ALL – The New Features of Release 16
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.