INFO:
select * from emp where deptno between :startt and :endd and (:emp_name is null or ename = :emp_name);
- The index columns are (deptno, ename).
- Both columns are nullable.
When i see a execution plan for the query above, the condition “(:emp_name is null or ename = :emp_name)” is used as table filter predicate rather than index filter predicate. I want to know the reason for it:
SQL_ID 87cbukvjc2n2f, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ * from emp where 1 = 1 and deptno
between :startt and :endd and (:emp_name is null or ename =
:emp_name)
Plan hash value: 514704104
------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 2 (100)| | 1 |00:00:00.01 | 2 |
|* 1 | FILTER | | 1 | | | | | 1 |00:00:00.01 | 2 |
|* 2 | TABLE ACCESS BY INDEX ROWID BATCHED| EMP | 1 | 1 | 38 | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 2 |
|* 3 | INDEX RANGE SCAN | EMP_X01 | 1 | 11 | | 1 (0)| 00:00:01 | 11 |00:00:00.01 | 1 |
------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(TO_NUMBER(:ENDD)>=TO_NUMBER(:STARTT))
2 - filter((:EMP_NAME IS NULL OR "ENAME"=:EMP_NAME))
3 - access("DEPTNO">=TO_NUMBER(:STARTT) AND "DEPTNO"<=TO_NUMBER(:ENDD))
I thought that the condition can be used as index filter predicate, because for the leaf blocks scanned by condition between :startt and :endd(with index access predicate), the range can be filtered with the condition (:emp_name is null or ename = :emp_name) by checking the :emp_name and ename values while using index only.
But for the check the condition (:emp_name is null or ename = :emp_name), why the table access is needed?
Thank you.