You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@trafodion.apache.org by "Suresh Subbiah (JIRA)" <ji...@apache.org> on 2017/07/06 00:20:00 UTC
[jira] [Closed] (TRAFODION-2622) Left join with
other_join_predicate on outer table alone is slow
[ https://issues.apache.org/jira/browse/TRAFODION-2622?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Suresh Subbiah closed TRAFODION-2622.
-------------------------------------
> Left join with other_join_predicate on outer table alone is slow
> ----------------------------------------------------------------
>
> Key: TRAFODION-2622
> URL: https://issues.apache.org/jira/browse/TRAFODION-2622
> Project: Apache Trafodion
> Issue Type: Improvement
> Components: sql-general
> Affects Versions: 2.0-incubating
> Reporter: Suresh Subbiah
> Assignee: Suresh Subbiah
>
> When a Left join has an equality predicate in the ON clause connecting both tables, but this predcate is not selective (causes a join explosion) AND there is anothe predicate in the ON clause that ion the outer table alone that is highly selective, then the query is slow.
> To reproduce
> create table imei_outer(imei char(15));
> create table imei_inner(imei char(15));
> create table imei_inner_null(imei char(15));
> upsert using load into imei_outer
> select 'imei' || cast(num as char(10))
> from (
> select 100000*e5+10000*e4+1000*e3+100*e2+10*e1+e0 as num
> from (values (0)) seed(c)
> transpose 0,1,2,3,4,5,6,7,8,9 as e0
> transpose 0,1,2,3,4,5,6,7,8,9 as e1
> transpose 0,1,2,3,4,5,6,7,8,9 as e2
> transpose 0,1,2,3,4,5,6,7,8,9 as e3
> transpose 0,1,2,3,4,5,6,7,8,9 as e4
> transpose 0,1,2,3,4,5,6,7,8,9 as e5)
> ;
> upsert using load into imei_inner
> select case when num >200000 then '' else 'imei' || cast(num as char(10)) end
> from (
> select 100000*e5+10000*e4+1000*e3+100*e2+10*e1+e0 as num
> from (values (0)) seed(c)
> transpose 0,1,2,3,4,5,6,7,8,9 as e0
> transpose 0,1,2,3,4,5,6,7,8,9 as e1
> transpose 0,1,2,3,4,5,6,7,8,9 as e2
> transpose 0,1,2,3,4,5,6,7,8,9 as e3
> transpose 0,1,2,3,4,5,6,7,8,9 as e4
> transpose 0,1,2,3,4,5,6,7,8,9 as e5)
> ;
> upsert using load into imei_inner_null
> select case when num >200000 then null else 'imei' || cast(num as char(10)) end
> from (
> select 100000*e5+10000*e4+1000*e3+100*e2+10*e1+e0 as num
> from (values (0)) seed(c)
> transpose 0,1,2,3,4,5,6,7,8,9 as e0
> transpose 0,1,2,3,4,5,6,7,8,9 as e1
> transpose 0,1,2,3,4,5,6,7,8,9 as e2
> transpose 0,1,2,3,4,5,6,7,8,9 as e3
> transpose 0,1,2,3,4,5,6,7,8,9 as e4
> transpose 0,1,2,3,4,5,6,7,8,9 as e5)
> ;
> prepare s from
> select count(*)
> from imei_outer o left join imei_inner_null i on substring(o.imei,1,14) = substring(i.imei,1,14) and o.imei > 'zzzzz' ;
--
This message was sent by Atlassian JIRA
(v6.4.14#64029)