DWHPro Insights

Optimize Teradata UNION ALL with a Single Table Scan Trick

By Roland Wenzlofsky ·

Optimize Teradata UNION ALL with a Single Table Scan Trick

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:

SELECT CloseDate FROM Customer
UNION ALL
SELECT OpenDate FROM Customer
;
teradata union all

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.

Teradata Query Performance Tuning: The DWHPro Guide book cover

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
NumResultRowsTotalIOCountAMPCpuTime
13464826170,005521013,0014177,29
13464826170,0010146110,0014460,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.

Explore Teradata performance tuning

Teradata Query Performance Tuning: The DWHPro Guide book cover

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

Follow DWHPro in Google to see our articles more often in Search.

Leave a Comment

Teradata Query Performance Tuning: The DWHPro Guide book cover

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

Protected by Security by CleanTalk and CleanTalk Anti-Spam