I create a composite index (DEPTNO and ENAME columns) on table SCOTT.EMP. Then I run the following SQL.
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)
Oracle is accessing the composite index by DEPTNO column only, and do a table level filtering, this is not effective enough. So I rewrite this SQL with NVL function, and check the 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, we get the SQL run much faster than before by rewriting it with NVL function.