It seems strange to me:
When all needed columns are in the index, filter predicates are expectedly applied to the index
select a,b from xt_test where a=1 and (:b is null or b = :b) ----------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | ----------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 2 | |* 1 | INDEX RANGE SCAN| PK_XT_TEST | 1 | 1 | 1 |00:00:00.01 | 2 | ----------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("A"=1) filter((:B IS NULL OR "B"=:B))
But if I add another column “PAD”, the filter moves to the table filters:
select a,b,pad from xt_test where a=1 and (:b is null or b = :b) --------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | Buffers | --------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 1 | 4 | |* 1 | TABLE ACCESS BY INDEX ROWID| XT_TEST | 1 | 1 | 1 | 4 | |* 2 | INDEX RANGE SCAN | PK_XT_TEST | 1 | 10 | 10 | 2 | --------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter((:B IS NULL OR "B"=:B)) 2 - access("A"=1)
As workaround we can use something like that:
select--+ NO_ELIMINATE_JOIN(t) NO_ELIMINATE_JOIN(t2@sel$2) gather_plan_statistics a,b,pad from xt_test t where t.rowid in ( select t2.rowid from xt_test t2 where a=1 and (:b is null or b = :b) ); Plan hash value: 1464320522 -------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows |Buffers | -------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 1 | 3 | | 1 | NESTED LOOPS | | 1 | 1 | 1 | 3 | |* 2 | INDEX RANGE SCAN | PK_XT_TEST | 1 | 1 | 1 | 2 | | 3 | TABLE ACCESS BY USER ROWID| XT_TEST | 1 | 1 | 1 | 1 | -------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("A"=1) filter((:B IS NULL OR "B"=:B))
Full test case
create table xt_test(a,b,pad,constraint pk_xt_test primary key(a,b)) as select mod(rownum,10) a ,rownum b ,rpad(rownum,10) pad from dual connect by level<=100; call dbms_stats.gather_table_stats('','XT_TEST'); var b number; exec :b:=1; select/*+ gather_plan_statistics */ a,b,pad from xt_test where a=1 and (:b is null or b = :b); select * from table(dbms_xplan.display_cursor('','','allstats last')); select/*+ gather_plan_statistics */ a,b from xt_test where a=1 and (:b is null or b = :b); select * from table(dbms_xplan.display_cursor('','','allstats last')); select--+ NO_ELIMINATE_JOIN(t) NO_ELIMINATE_JOIN(t2@sel$2) gather_plan_statistics a,b,pad from xt_test t where t.rowid in ( select t2.rowid from xt_test t2 where a=1 and (:b is null or b = :b) ); select * from table(dbms_xplan.display_cursor('','','allstats last'));
[collapse]
Update:
I just forgot to mention that there is another workaround – to force concatenation:
SQL> select--+ use_concat(or_predicates(2)) 2 a,b,pad 3 from xt_test where a=1 and (:b is null or b = :b); A B PAD ---------- ---------- ---------------------------------------- 1 1 1 Plan hash value: 3582916188 ---------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers | Reads | ---------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 3 (100)| 1 |00:00:00.01 | 2 | 2 | | 1 | CONCATENATION | | 1 | | | 1 |00:00:00.01 | 2 | 2 | | 2 | TABLE ACCESS BY INDEX ROWID | XT_TEST | 1 | 1 | 1 (0)| 1 |00:00:00.01 | 2 | 2 | |* 3 | INDEX UNIQUE SCAN | PK_XT_TEST | 1 | 1 | 0 (0)| 1 |00:00:00.01 | 1 | 1 | |* 4 | FILTER | | 1 | | | 0 |00:00:00.01 | 0 | 0 | | 5 | TABLE ACCESS BY INDEX ROWID| XT_TEST | 0 | 10 | 2 (0)| 0 |00:00:00.01 | 0 | 0 | |* 6 | INDEX RANGE SCAN | PK_XT_TEST | 0 | 10 | 1 (0)| 0 |00:00:00.01 | 0 | 0 | ---------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("A"=1 AND "B"=:B) 4 - filter(:B IS NULL) 6 - access("A"=1) filter(LNNVL("B"=:B))