12.1.0.2
A shortened version of a bigger problem
drop table t1
create table t1(
col1 number,
col2 number,
col3 number);
create index i1 on t1(col1);
create index i2 on t1(col2);
create index i3 on t1(col3);
insert into t1 values (1, null, null);
insert into t1 values (2, 1, null);
insert into t1 values (3, null, 1);
exec dbms_Stats.gather_Table_Stats(user, 't1');
Now a query can come in where any of the columns could be the driving predicate, we could even have a combination of them so we get queries like this being sent in (binds are in use which will become clear )
exec :b1:=2
exec :b2:=''
exec :b3:=''
select distinct col1, col2 from t1
where
(col1 = :b1 or :b1 is null)
and
(col2 = :b2 or :b2 is null)
and
(col3 = :b2 or :b3 is null)
;
run with autotrace on and we see this generated a full table scan, but we want it to use the i1 index
Execution Plan
----------------------------------------------------------
Plan hash value: 2134347679
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 7 | 4 (25)| 00:00:01 |
| 1 | HASH UNIQUE | | 1 | 7 | 4 (25)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| T1 | 1 | 7 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
The SQL is in a package. If I run the same query but use the actual value of b1, the index is used
exec :b1:=2
exec :b2:=''
exec :b3:=''
select distinct col1, col2 from t1
where
(col1 = 2 or 2 is null)
and
(col2 = :b2 or :b2 is null)
and
(col3 = :b2 or :b3 is null)
;
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 2675825682
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 7 | 3 (34)| 00:00:01 |
| 1 | HASH UNIQUE | | 1 | 7 | 3 (34)| 00:00:01 |
|* 2 | TABLE ACCESS BY INDEX ROWID BATCHED| T1 | 1 | 7 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | I1 | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter((:B2 IS NULL OR "COL2"=TO_NUMBER(:B2)) AND (:B3 IS NULL OR
"COL3"=TO_NUMBER(:B2)))
3 - access("COL1"=2)
So this is Im guessing due to oracle not peeking at the bind before it decides what path its going to take, it doesnt know it could be true so decides to look at the entire table.
We could have some if-then-else statements and run a different query depending on whats populated but as this is a small example of a query that has 10 predicates the combinations to code for therein would be horrible to code for all.
Any suggestions that dont include messing with cursor_sharing?