Major Description
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:
>>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
