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:
Want more practical data engineering analysis like this?
Join DWHPro Letters and get field-tested notes on Teradata, Snowflake, AI, migrations, performance, and enterprise data work. DWHPro Letters is free. Subscribe to get new issues by email.
- 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
Prior to Teradata 15.10, the Optimizer did not employ any reduction, such as WHERE conditions, until all branches of the statement including UNION ALL were consolidated in a shared spool.
Get the next issue by email.
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
This method will require fewer resources than the original approach if the partial join stages result in smaller spools for each partial join.
Aggregations can be applied to each UNION ALL branch instead of consolidating all branches into a single spool before aggregation, which was the only option for the Optimizer before Teradata 15.10. The performance enhancement can be substantial if partial aggregations result in smaller spool files.
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.
Planning or surviving an enterprise data platform migration?
I write regularly about the performance, cost, architecture, and project mistakes that show up in real Teradata, Snowflake, Databricks, and enterprise data work.
Subscribe for free and keep launch access.
Written by Roland Wenzlofsky, founder of DWHPro and author of Teradata Query Performance Tuning. DWHPro has helped data warehouse practitioners for 15+ years.