Joshua Liu created TRAFODION-2136: ------------------------------------- Summary: union all can't work efficiently Key: TRAFODION-2136 URL: https://issues.apache.org/jira/browse/TRAFODION-2136 Project: Apache Trafodion Issue Type: Bug Components: sql-exe Affects Versions: any Reporter: Joshua Liu
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. -- This message was sent by Atlassian JIRA (v6.3.4#6332)