[
https://issues.apache.org/jira/browse/TRAFODION-2622?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16074945#comment-16074945
]
ASF GitHub Bot commented on TRAFODION-2622:
-------------------------------------------
Github user zellerh commented on a diff in the pull request:
https://github.com/apache/incubator-trafodion/pull/1163#discussion_r125673665
--- Diff: core/sql/generator/GenPreCode.cpp ---
@@ -2459,6 +2459,24 @@ RelExpr * Join::preCodeGen(Generator * generator,
}
+ // if we have both equi join preds and a beforejoin pred
+ // Set a flag that will cause beforeJoinPred to be evaluated prior
+ // join equi pred during execution. This helps with join explosion
+ // if there are frequent matching values and the beforeJoinPred is
+ // highly selective. There is no downside to evaluating
beforeJoinPred
+ // early, if it contains vids from outer only
+ if (!(getEquiJoinPredicates().isEmpty() || getJoinPred().isEmpty()
||
+ isAntiSemiJoin()))
+ {
+ ValueIdSet dummy1, dummy2, dummy3, uncoveredPreds ;
--- End diff --
Just for a future checkin, I think dummy3 could be removed.
> 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)
