[
https://issues.apache.org/jira/browse/TRAFODION-2622?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16073833#comment-16073833
]
ASF GitHub Bot commented on TRAFODION-2622:
-------------------------------------------
GitHub user sureshsubbiah opened a pull request:
https://github.com/apache/incubator-trafodion/pull/1163
[TRAFODION-2622] Left join with other_join_predicate on table alone i…
…s slow
This change is due to Hans Zeller.
The other join predicate in a hash join is evaluated before the hash join,
so that failing rows can be nulll instantiated directly. This avoids
a temporary join explosion when the join predicate matches most rows but
the other_join_predicate fails most rows. This change will be extended
to merge joins later.
You can merge this pull request into a Git repository by running:
$ git pull https://github.com/sureshsubbiah/incubator-trafodion 2622
Alternatively you can review and apply these changes as the patch at:
https://github.com/apache/incubator-trafodion/pull/1163.patch
To close this pull request, make a commit to your master/trunk branch
with (at least) the following in the commit message:
This closes #1163
----
commit de3348941e05c8afaa731af3798aaea46ac40cac
Author: Suresh Subbiah <[email protected]>
Date: 2017-07-04T15:31:12Z
[TRAFODION-2622] Left join with other_join_predicate on table alone is slow
This change is due to Hans Zeller.
The other join predicate in a hash join is evaluated before the hash join,
so that failing rows can be nulll instantiated directly. This avoids
a temporary join explosion when the join predicate matches most rows but
the other_join_predicate fails most rows. This change will be extended
to merge joins later.
----
> 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)
