[
https://issues.apache.org/jira/browse/TRAFODION-2622?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Suresh Subbiah resolved TRAFODION-2622.
---------------------------------------
Resolution: Fixed
> 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)
