Hi all,
   This is a simple scenario, there are two tables:
   create table t1 (pk integer primary key, a integer);
   create table t2 (pk1 integer not null, pk2 integer not null, a integer 
constraint pk primary key (pk1, pk2));

   Do the following selects:
  1. explain select * from t1 where (pk > 10 and pk < 20) or (pk > 30 and pk < 
  results:     SKIP SCAN ON 2 RANGES OVER T1 [11] - [40]
  2. explain select * from t2 where (pk1 > 10 and pk1 < 20) or (pk1 > 30 and 
pk1 < 40);
  results:     FULL SCAN OVER T2 
                   SERVER FILTER BY ((PK1 > 10 AND PK1 < 20) OR (PK1 > 30 AND 
PK1 < 40))

    Apparently, 2nd SELECT statement should use skip scan instead of full table 
scan. But T2 has two pk columns and then WhereOptimizer failed to optimize it. 
I went through the code and made a small improvement.
    In WhereOptimizer#KeyExpressionVisitor#orKeySlots(),  see the attached 
patch file for detail. The main idea is we allow slot in childSlot is null, 
only if all slots afterwards are null too. So the following statements are 
still rejected:
    select * from t2 where (pk1 > 10 and pk1 < 20)  or (pk2 > 30 and pk2 < 40)

Please review this. Thanks.

Reply via email to