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))
As you see, in such queries cbo always generating plans with INLIST ITERATOR, and it is reasonably in cases when there are many rows with different values of field B for most values of A, and this number is much larger than number of values in the “INLIST”. But in such case as shown, will be better to use index range scan with access by A and filter by B:
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 4 options:
1. Rewrite code. for example replacing “b in (1,2)” to “b+0 in (1,2)”
2. Changing query with “Advanced query rewrite” (DBMS_ADVANCED_REWRITE.DECLARE_REWRITE_EQUIVALENCE)
3. Recreating index from xt2(a,b) to xt2(a,1,b)
4. 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?