对SCOTT用户EMP表的DEPTNO和ENAME字段建了一个复合索引. 在SQL的WHERE子句中用了一个OR条件, 看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)

    将SQL语句中的OR条件去掉, 再看Plan:

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')

    其实在真实的案例中, 记录中ENAME字段已经没有空值了.