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/03 20:19:01 UTC
[jira] [Updated] (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 updated TRAFODION-2622:
--------------------------------------
Description:
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' ;
was:
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 = 'happy' ;
> 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)