Tag Archives: inlist iterator

A couple days ago i had very interesting quiz, which is not resolved yet.
Look at this simplified query:

  select *
  from xt1,xt2
  where
       xt1.b=10
   and xt1.a=xt2.a
   and xt2.b in (1,2);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------
Plan hash value: 2715236140

----------------------------------------------------------------------------------------
| Id  | Operation                     | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |        |   100 | 36900 |   501   (0)| 00:00:07 |
|   1 |  NESTED LOOPS                 |        |       |       |            |          |
|   2 |   NESTED LOOPS                |        |   100 | 36900 |   501   (0)| 00:00:07 |
|   3 |    TABLE ACCESS BY INDEX ROWID| XT1    |   100 | 31000 |   101   (0)| 00:00:02 |
|*  4 |     INDEX RANGE SCAN          | IX_XT1 |   100 |       |     1   (0)| 00:00:01 |
|   5 |    INLIST ITERATOR            |        |       |       |            |          |
|*  6 |     INDEX RANGE SCAN          | IX_XT2 |     1 |       |     3   (0)| 00:00:01 |
|   7 |   TABLE ACCESS BY INDEX ROWID | XT2    |     1 |    59 |     4   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("XT1"."B"=10)
   6 - access("XT1"."A"="XT2"."A" AND ("XT2"."B"=1 OR "XT2"."B"=2))

create table xt1 as
select 
   level a
 , mod(level,1000) b
 , lpad(1,300,1) padding 
from dual
connect by level<=1e5;

create index ix_xt1 on xt1(b);

create table xt2 as
select 
   level        a
 , mod(level,5) b
 , lpad(1,50,1) padding 
from dual
connect by level<=1e6;

alter table xt2 
  add constraint uq_xt2
  unique (a)
  using index(create index ix_xt2 on xt2(a,b));

exec dbms_stats.gather_table_stats('','XT1',cascade=>true);
exec dbms_stats.gather_table_stats('','XT2',cascade=>true);

explain plan for 
select *
from xt1,xt2
where 
     xt1.b=10
 and xt1.a=xt2.a
 and xt2.b in (1,2);

@?/rdbms/admin/utlxpls.sql

SQL> select *
  2  from xt1,xt2
  3  where
  4       xt1.b=10
  5   and xt1.a=xt2.a
  6   and xt2.b in (1,2);

no rows selected

Statistics
----------------------------------------------------------
        ...
        505  consistent gets
SQL> -- without inlist iterator:
SQL> select *
  2  from xt1,xt2
  3  where
  4       xt1.b=10
  5   and xt1.a=xt2.a
  6   and xt2.b+0 in (1,2);

no rows selected

Statistics
----------------------------------------------------------
        ...
        305  consistent gets

But how we can do it? I know 5 options:
1. Trace event 10157
2. Rewrite code. for example replacing “b in (1,2)” to “b+0 in (1,2)”
3. Changing query with “Advanced query rewrite” (DBMS_ADVANCED_REWRITE.DECLARE_REWRITE_EQUIVALENCE)
4. Recreating index from xt2(a,b) to xt2(a,1,b)
5. Changing optimizer_mode to “rule” through hint or SQL profile/baseline

But unfortunately all of them are inapplicable for the my real problem, because i cannot for some reasons rewrite query or change query with advanced rewrite, cannot recreate/add index, and can’t change optimizer_mode, because execution plan for the real query will become worst than plan generated with CBO with inlist iterator(some operations aren’t exists in RBO).

Could anybody suggest any another solution?

UPDATE #1:
This bug is fixed now in 12.2, and patch 16516751 is available now for 11.2.0.3 Solaris64.
Changes:
1. CBO can consider filters in such cases now
2. Hint NUM_INDEX_KEYS fixed and works fine

UPDATE #2:
Very interesting solution by Igor Usoltsev(in russian):
Ignored hint USE_CONCAT(OR_PREDICATES(N)) allows to avoid inlist iterator.
Example:

select--+ USE_CONCAT(OR_PREDICATES(32767))
 * from xt1,xt2
where
     xt1.b=10
 and xt1.a=xt2.a
 and xt2.b in (1,2)
/

Plan hash value: 2884586137          -- good plan:
 
----------------------------------------------------------------------------------------
| Id  | Operation                     | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |        |       |       |   401 (100)|          |
|   1 |  NESTED LOOPS                 |        |       |       |            |          |
|   2 |   NESTED LOOPS                |        |   100 | 36900 |   401   (0)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| XT1    |   100 | 31000 |   101   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | IX_XT1 |   100 |       |     1   (0)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN           | IX_XT2 |     1 |       |     2   (0)| 00:00:01 |
|   6 |   TABLE ACCESS BY INDEX ROWID | XT2    |     1 |    59 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   4 - access("XT1"."B"=10)
   5 - access("XT1"."A"="XT2"."A")
       filter(("XT2"."B"=1 OR "XT2"."B"=2)) 

From 10053 trace on nonpatched 11.2.0.3: