[TRAFODION-2136] union all can't work efficiently

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Duplicate
• Affects Version/s: any
• Component/s: sql-exe
• Flags: Important
• Assignee: liu ming
• Reporter: Joshua Liu
• Created: 30/Jul/16 01:58
• Updated: 09/Sep/18 03:08
• Resolved: 09/Sep/18 03:08

Description

there is one query called a
the first time we run
a;
and get the elapsed time time1
reconnect the database;
the second time we run
a union all a;
and get another elapsed time.time2
here time2 ≈ time1*2
---------------------------------------------
see below for detailed info
a is
select count
from F_INCOME_DAY_RH a left join D_IN_SORTCODE_NEW b
on (A.INCOMESORTCODE_N = B.CODE1)
where (((a.BBQ >= date '2014-01-01') AND (a.BBQ <= date '2014-12-31')) AND
((a.BUDGETLEVEL = '3') AND
((a.TAXORGCODE in ('1', '2', '3', '4', '5'))) AND
(a.DISTRICT_CO_CODE like 'A%' or
a.DISTRICT_CO_CODE like 'B%')) AND
((a.INCOMESORTCODE_N LIKE 'A%') OR
(a.INCOMESORTCODE_N LIKE 'B%') OR
(a.INCOMESORTCODE_N LIKE 'C%') OR
(a.INCOMESORTCODE_N LIKE 'F%') OR
(a.INCOMESORTCODE_N LIKE 'G%')))
group by b.CODE5
---------
SQL>explain options 'f' s1;

LC RC OP OPERATOR OPT DESCRIPTION CARD
---- ---- ---- -------------------- -------- -------------------- ---------

7 . 8 root 6.00E+000
6 . 7 hash_partial_groupby 6.00E+000
5 . 6 esp_exchange 1:16(hash2) 6.00E+000
4 . 5 hash_partial_groupby 6.00E+000
3 2 4 left_hybrid_hash_joi 1.06E+006
. . 3 trafodion_scan F_INCOME_DAY_RH 1.06E+006
1 . 2 esp_exchange 16(rep-b):1 (m) 7.24E+002
. . 1 trafodion_scan D_IN_SORTCODE_NEW 7.24E+002

— SQL operation complete.

SQL>set statistics on;

SQL>execute s1;

(EXPR)
--------------------
977626
16080
34817
53722
5573

— 5 row(s) selected.

Start Time 2016/07/30 09:47:50.913298
End Time 2016/07/30 09:48:05.018125
Elapsed Time 00:00:14.104827
Compile Time 00:00:05.641437
Execution Time 00:00:14.104827

---------------------------------------------------------------------------
reconnect database and run a union all a;

SQL>explain options 'f' s1;

LC RC OP OPERATOR OPT DESCRIPTION CARD
---- ---- ---- -------------------- -------- -------------------- ---------

15 . 16 root 1.20E+001
7 14 15 merge_union 1.20E+001
13 . 14 hash_partial_groupby 6.00E+000
12 . 13 esp_exchange 1:16(hash2) 6.00E+000
11 . 12 hash_partial_groupby 6.00E+000
10 9 11 left_hybrid_hash_joi 1.06E+006
. . 10 trafodion_scan F_INCOME_DAY_RH 1.06E+006
8 . 9 esp_exchange 16(rep-b):1 (m) 7.24E+002
. . 8 trafodion_scan D_IN_SORTCODE_NEW 7.24E+002
6 . 7 hash_partial_groupby 6.00E+000
5 . 6 esp_exchange 1:16(hash2) 6.00E+000
4 . 5 hash_partial_groupby 6.00E+000
3 2 4 left_hybrid_hash_joi 1.06E+006
. . 3 trafodion_scan F_INCOME_DAY_RH 1.06E+006
1 . 2 esp_exchange 16(rep-b):1 (m) 7.24E+002
. . 1 trafodion_scan D_IN_SORTCODE_NEW 7.24E+002

— SQL operation complete.

Start Time 2016/07/30 09:48:28.230061
End Time 2016/07/30 09:48:45.506429
Elapsed Time 00:00:17.276368
Compile Time 00:00:17.276368
Execution Time 00:00:00.000000

SQL>execute s1;

(EXPR)
--------------------
977626
16080
34817
53722
5573
977626
16080
34817
53722
5573

— 10 row(s) selected.

Start Time 2016/07/30 09:50:39.754195
End Time 2016/07/30 09:51:10.680951
Elapsed Time 00:00:30.926756
Compile Time 00:00:17.276368
Execution Time 00:00:30.926756

-------------------------------------------------------

here we can see the time is 30.9 versus 14.1
and from the plan, seems it's running in parallel. but why it uses so much time in union all case?
apparently there is something wrong here.

Issue Links

• duplicates: TRAFODION-3192 UNION ALL up and down queue size default value too small, or not dynamically adjusted