The Idea Behind This Trick for Teradata UNION ALL
What if you need to apply a UNION ALL operation to distinct columns within a single table? Typically, the process would involve:
Part of the Teradata SQL Tuning and Query Rewriting guide.
SELECT CloseDate FROM Customer
UNION ALL
SELECT OpenDate FROM Customer
;

The rewrite may permit one base-table scan, but scan count and I/O benefit depend on the optimizer and data. Verify the plan rather than assuming every UNION ALL form scans twice.
SELECT
CASE
WHEN t02.NBR = 1 THEN t01.CloseDate
WHEN t02.NBR = 2 THEN t01.OpenDate
ELSE NULL
END
FROM Customer t01
CROSS JOIN
(
SELECT x AS NBR
FROM (SELECT 1 AS x) x
UNION ALL
SELECT x AS NBR
FROM (SELECT 2 AS x) x
) t02
WHERE t02.NBR IN (1,2)
;
This query only requires one complete table scan and significantly reduces IOs, particularly for massive tables.
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| NumResultRows | TotalIOCount | AMPCpuTime |
| 13464826170,00 | 5521013,00 | 14177,29 |
| 13464826170,00 | 10146110,00 | 14460,84 |
The IOs have significantly decreased from 10146110.00 to 5521013.00, nearly a 50% reduction.
The Same Trick for UNION
Adding DISTINCT simulates UNION through column selection.
SELECT DISTINCT
CASE
WHEN t02.NBR = 1 THEN t01.CloseDate
WHEN t02.NBR = 2 THEN t01.OpenDate
ELSE NULL
END
FROM Customer t01
CROSS JOIN
(
SELECT x AS NBR
FROM (SELECT 1 AS x) x
UNION ALL
SELECT x AS NBR
FROM (SELECT 2 AS x) x
) t02
WHERE t02.NBR IN (1,2)
;
The equivalent standard UNION query looks like this:
SELECT CloseDate FROM Customer
UNION
SELECT OpenDate FROM Customer
;
I cannot provide specific figures as the UNION variant terminated due to a “no more spool space” error, likely caused by the query’s skewness.
The results should be comparable to the DISTINCT-based approach above.
Additionally, refer to:
Validation scope: The original and CASE expansion returned identical NULL and duplicate multiplicities in a fixture with compatible types. The historical large-table I/O figures were not reproduced, and the proposed cause of the UNION spool failure remains speculation.
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