The Idea Behind This Trick for Teradata UNION ALL
What if you want to perform a UNION ALL on two different columns of the same table? Normally you would do this:
SELECT CloseDate FROM Customer UNION ALL SELECT OpenDate FROM Customer ;

However, this method has a disadvantage:
The table Customer must be queried with two full table scans. So I looked for a method that works with only one full table scan and without UNION ALL but gives the same result.
A possible solution I found is the following:
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 needs only one full table scan and saves a lot of IOs, especially for huge tables.
NumResultRows | TotalIOCount | AMPCpuTime |
13464826170,00 | 5521013,00 | 14177,29 |
13464826170,00 | 10146110,00 | 14460,84 |
As you can see, the number of IOs has been reduced from 10146110.00 to 5521013.00, almost halved!
The Same Trick for UNION
UNION can easily be simulated by using DISTINCT in the column selection list:
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) ;
Here is the query which uses the UNION functionality:
SELECT CloseDate FROM Customer UNION SELECT OpenDate FROM Customer ;
Unfortunately, I can’t provide concrete numbers here because the UNION variant aborted with a “no more spool space” error. This happens because the query is skewed.
But the results should be similar.
See also: