I created a composite index on DEPTNO and ENAME columns for EMP table under SCOTT schema. Then run a SQL with OR in the where clause, check the execute plan:
SQL> SELECT /*+ first_rows no_expand */ * FROM EMP
2 WHERE deptNO=10 AND (ENAME IS NULL OR ENAME > 'A');
---------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |
---------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 330 |
|* 1 | TABLE ACCESS BY INDEX ROWID| EMP | 3 | 330 |
|* 2 | INDEX RANGE SCAN | IDX_EMP | 3 | |
---------------------------------------------------------------
1 - filter("ENAME" IS NULL OR "ENAME">'A')
2 - access("DEPTNO"=10)
The filter operation performed on the table access. Then I rewrite the SQL with a NVL function, and check execution plan again :
SQL> SELECT /*+ first_rows no_expand */ * FROM EMP
2 WHERE deptNO=10 AND NVL(ENAME,'B') > 'A';
---------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |
---------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 330 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 3 | 330 |
|* 2 | INDEX RANGE SCAN | IDX_EMP | 1 | |
---------------------------------------------------------------
2 - access("DEPTNO"=10)
filter(NVL("ENAME",'B')>'A')
In a real case of our database, the first SQL need more than 10k consistent gets, while the second just need 150 consistent gets.