Suresh Subbiah created TRAFODION-2622:
-----------------------------------------
Summary: 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 = 'happy' ;
--
This message was sent by Atlassian JIRA
(v6.3.15#6346)
