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
;

The drawback of this method is that it scans the Customer table twice. To achieve the same output with just one full table scan, here is an alternative approach:

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.

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:

Teradata Set Operators: Understanding UNION vs. UNION ALL for Peak Performance Optimization