fbpx

The Idea Behind This Trick for 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
;
teradata union all

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.

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

Teradata UNION ALL Enhancements

__CONFIG_colors_palette__{"active_palette":0,"config":{"colors":{"62516":{"name":"Main Accent","parent":-1}},"gradients":[]},"palettes":[{"name":"Default Palette","value":{"colors":{"62516":{"val":"var(--tcb-skin-color-0)"}},"gradients":[]},"original":{"colors":{"62516":{"val":"rgb(19, 114, 211)","hsl":{"h":210,"s":0.83,"l":0.45}}},"gradients":[]}}]}__CONFIG_colors_palette__
__CONFIG_colors_palette__{"active_palette":0,"config":{"colors":{"b4fbe":{"name":"Main Accent","parent":-1}},"gradients":[]},"palettes":[{"name":"Default Palette","value":{"colors":{"b4fbe":{"val":"rgb(241, 99, 52)"}},"gradients":[]},"original":{"colors":{"b4fbe":{"val":"rgb(19, 114, 211)","hsl":{"h":210,"s":0.83,"l":0.45}}},"gradients":[]}}]}__CONFIG_colors_palette__
Previous Article
__CONFIG_colors_palette__{"active_palette":0,"config":{"colors":{"b4fbe":{"name":"Main Accent","parent":-1}},"gradients":[]},"palettes":[{"name":"Default Palette","value":{"colors":{"b4fbe":{"val":"rgb(241, 99, 52)"}},"gradients":[]},"original":{"colors":{"b4fbe":{"val":"rgb(19, 114, 211)","hsl":{"h":210,"s":0.83,"l":0.45}}},"gradients":[]}}]}__CONFIG_colors_palette__
Next Article
__CONFIG_colors_palette__{"active_palette":0,"config":{"colors":{"62516":{"name":"Main Accent","parent":-1}},"gradients":[]},"palettes":[{"name":"Default Palette","value":{"colors":{"62516":{"val":"rgb(255, 0, 0)"}},"gradients":[]}}]}__CONFIG_colors_palette__
GET OUR TERADATA BOOK

DWH Pro Admin

Roland Wenzlofsky is an experienced freelance Teradata Consultant & Performance Trainer. Born in Austria's capital Vienna, he is building and tuning some of the largest Teradata Data Warehouses in the European financial and telecommunication sectors for more than 20 years. He has played all the roles of developer, designer, business analyst, and project manager. Therefore, he knows like no other the problems and obstacles that make many data warehouse projects fail and all the tricks and tips that will help you succeed.

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

You might also like

>