To make a long story short, let me show a very simple test case. 1. Create tables and indices create table t1(c1 number, c2 number, c3 number); > create index t1_n1 on t1(c1); > exec dbms_stats.gather_table_stats(user, 't1'); 2. The condition of { between :b1 and :b2 } is used as the ACCESS predicate. > var b1 number; var b2 number; var b3 number; > explain plan for select * from t1 where c1 between :b1 and :b2 and c1 > :b3 ; -------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 39 | 0 (0)| 00:00:01 | |* 1 | FILTER | | | | | | | 2 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 39 | 0 (0)| 00:00:01 | |* 3 | INDEX RANGE SCAN | T1_N1 | 1 | | 0 (0)| 00:00:01 | -------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(TO_NUMBER(:B2)>TO_NUMBER(:B3) AND TO_NUMBER(:B1)<=TO_NUMBER(:B2)) 3 - *access("C1">=TO_NUMBER(:B1) AND "C1"<=TO_NUMBER(:B2)) <-- Here!* filter("C1">TO_NUMBER(:B3)) 3. But with additional IN operation(which has no relation with pre-existent BETWEEN operation), the { c1 > :b3 and c1 <= :b2 } is used as the ACCESS predicate. explain plan for select * from t1 where c1 between :b1 and :b2 and c2 in (1, 2, 3) and c1 > :b3 ; -------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 39 | 0 (0)| 00:00:01 | |* 1 | FILTER | | | | | | |* 2 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 39 | 0 (0)| 00:00:01 | |* 3 | INDEX RANGE SCAN | T1_N1 | 1 | | 0 (0)| 00:00:01 | -------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(TO_NUMBER(:B2)>TO_NUMBER(:B3) AND TO_NUMBER(:B1)<=TO_NUMBER(:B2)) 2 - filter("C2"=1 OR "C2"=2 OR "C2"=3) 3 - *access("C1">TO_NUMBER(:B3) AND "C1"<=TO_NUMBER(:B2)) <-- Here* filter("C1">=TO_NUMBER(:B1)) This is a very unexpected change whose reason I couldn't identify. I've dumped 10053 trace for the additional info but got nothing meaningful. I'm not sure whether this is a designed feature or just an unexpected bug. The only certain thing is that this has caused unexpected performance degradation while the developers change the SQL code. :( Can anyone share some knowledges and experiences and comments on this?