[
https://issues.apache.org/jira/browse/TRAFODION-3325?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
David Wayne Birdsall resolved TRAFODION-3325.
---------------------------------------------
Fix Version/s: 2.4
Resolution: Fixed
> Inefficient plan when using a join to a tuple list
> --------------------------------------------------
>
> Key: TRAFODION-3325
> URL: https://issues.apache.org/jira/browse/TRAFODION-3325
> Project: Apache Trafodion
> Issue Type: Bug
> Components: sql-cmp
> Reporter: David Wayne Birdsall
> Assignee: David Wayne Birdsall
> Priority: Major
> Fix For: 2.4
>
> Attachments: repro.sql.txt
>
> Time Spent: 2h 50m
> Remaining Estimate: 0h
>
> In the example below, statement s1 gets a hash join plan that does a full
> scan on table T1, even though a far more efficient nested join plan using
> index IT1 is possible. The problem is that the Optimizer is not considering
> an index scan using IT1 when the join predicates are between T1 and a tuple
> list.
> In contrasting examples below where we use an IN list instead of a join to a
> tuple list, and where we join to a table T2 instead of a tuple list, we do
> get efficient index access plans.
> To reproduce, execute the attached script to create the test tables and
> compile the test queries.
> When executed, we see the following plans:
> {quote}>>obey repro.sql(testit);
> >>
> >>prepare s1 from select t1.* from t1 join (values (3),(4)) f(b) on t1.b =
> >>f.b;
> --- SQL command prepared.
> >>explain options 'f' s1;
> LC RC OP OPERATOR OPT DESCRIPTION CARD
> ---- ---- ---- -------------------- -------- -------------------- ---------
> 5 . 6 root 4.00E+002
> 4 . 5 esp_exchange 1:2(hash2) 4.00E+002
> 3 2 4 hybrid_hash_join 4.00E+002
> . . 3 trafodion_scan T1 2.00E+006
> 1 . 2 esp_exchange 2(rep-b):1 2.00E+000
> . . 1 tuplelist 2.00E+000
> --- SQL operation complete.
> >>showshape select * from t1 join (values (3),(4)) f(b) on t1.b = f.b;
> control query shape esp_exchange(hybrid_hash_join(
> scan(path 'TRAFODION.SCH.T1', forward, blocks_per_access 489 , mdam off),
> esp_exchange(anything)));
> --- SQL operation complete.
> >>
> >>prepare s2 from select t1.* from t1 where b in (3,4);
> --- SQL command prepared.
> >>explain options 'f' s2;
> LC RC OP OPERATOR OPT DESCRIPTION CARD
> ---- ---- ---- -------------------- -------- -------------------- ---------
> 3 . 4 root 4.00E+002
> 1 2 3 nested_join 4.00E+002
> . . 2 trafodion_vsbb_scan T1 1.00E+000
> . . 1 trafodion_index_scan IT1 4.00E+002
> --- SQL operation complete.
> >>showshape select * from t1 where b in (3,4);
> control query shape nested_join(scan(path 'TRAFODION.SCH.IT1', forward
> , blocks_per_access 1 , mdam forced, mdam_columns (dense, sparse)),
> scan(path 'TRAFODION.SCH.T1', forward, blocks_per_access 400 , mdam off)
> ,INDEXJOIN);
> --- SQL operation complete.
> >>
> >>prepare s3 from select t1.* from t1 join t2 on t1.b = t2.b where t2.b in
> >>(3,4);
> --- SQL command prepared.
> >>explain options 'f' s3;
> LC RC OP OPERATOR OPT DESCRIPTION CARD
> ---- ---- ---- -------------------- -------- -------------------- ---------
> 5 . 6 root 4.00E+002
> 1 4 5 nested_join 4.00E+002
> 2 3 4 nested_join 2.00E+002
> . . 3 trafodion_vsbb_scan T1 1.00E+000
> . . 2 trafodion_index_scan IT1 2.00E+002
> . . 1 trafodion_scan T2 2.00E+000
> --- SQL operation complete.
> >>showshape select t1.* from t1 join t2 on t1.b = t2.b where t2.b in (3,4);
> control query shape nested_join(scan(path 'TRAFODION.SCH.T2', forward
> , blocks_per_access 2 , mdam forced, mdam_columns all(dense)),nested_join(
> scan(path 'TRAFODION.SCH.IT1', forward, blocks_per_access 1 , mdam off),
> scan(path 'TRAFODION.SCH.T1', forward, blocks_per_access 400 , mdam off)
> ,INDEXJOIN));
> --- SQL operation complete.
> >>
> >>exit;
> End of MXCI Session
> {quote}
>
--
This message was sent by Atlassian Jira
(v8.3.2#803003)
