[
https://issues.apache.org/jira/browse/TRAFODION-2136?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16608281#comment-16608281
]
liu ming commented on TRAFODION-2136:
-------------------------------------
TRAFODION-3192 treat this issue. Thanks Sandhya.
> 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
> Priority: Major
>
> 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
(v7.6.3#76005)
