[ https://issues.apache.org/jira/browse/TRAFODION-2136?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
liu ming reassigned TRAFODION-2136: ----------------------------------- Assignee: liu ming > 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 > Assignee: liu ming > > 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)