Bug about which i wrote previously 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
UPD: 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:
