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.
Part of the Teradata SQL Tuning and Query Rewriting guide.
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.
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 bookBeginning 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.
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
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.
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.
🙂