Teradata UNION ALL Introduction
Teradata introduced a new Optimizer feature for managing sets of rows combined by UNION ALL in views and derived tables.
Part of the Teradata SQL Tuning and Query Rewriting guide.
UNION ALL branch pushdown and join/aggregation placement are optimizer- and release-dependent. These historical plans do not establish that earlier releases never pushed predicates or that every current query uses branch-local joins.
The separate branch SELECT statements illustrate independent branches, not a complete replacement request. To recombine them, use UNION ALL and an explicit identical projection matching the view; SELECT * can expose extra columns.
REPLACE VIEW TestView AS
(
SELECT PK, COL1, COL2 FROM TheTableA
UNION ALL
SELECT PK, COL1, COL2 FROM TheTableB
) ;
SELECT *
FROM
TestView T01
INNER JOIN
TestTable T02
ON
T01.PK = T02.PK
WHERE
T01.COL2 = 100;
The Teradata 15.10 Optimizer creates a common spool and performs a join operation with the “TestTable”.
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 bookThe Optimizer in Teradata 16 enables the division of a join into several segments and offers an additional choice.
Each Teradata UNION ALL branch can be joined separately to the “TestTable”. The final outcome of these join processes is stored in a final spool.
To illustrate the equivalent queries, the Teradata 16 Optimizer effectively executes:
SELECT * FROM TheTableA T01
INNER JOIN
TestTable T02
ON T01.PK = T02.PK
WHERE T01.COL2 = 100;
SELECT * FROM TheTableB T01
INNER JOIN
TestTable T02
ON T01.PK = T02.PK
WHERE T01.COL2 = 100;
This option can save resources by effectively decreasing the selected row count in every join.
The potential for decreased resource consumption rises as tables become larger and are merged using UNION ALL before being joined with an additional table.
The Teradata 16 Optimizer can apply aggregation to each branch of UNION ALL separately and then execute global aggregation in a subsequent step.
Starting with Teradata 14.10, the Optimizer has implemented a uniform optimization method for individual tables. This entails computing the row count on the Cylinder Index for each table linked by a UNION ALL operation and subsequently summing up the results. This technique remains in use in Teradata 16.
The Optimizer can utilize the aforementioned techniques to enhance performance by reducing spool sizes by pushing aggregation or joining into each UNION ALL branch.
The Optimizer heavily relies on statistics to determine the feasibility of implementing these methods.
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