[SQL tuning] Oracle 使用 NVL function 取代 原有的 OR 陳述式

Tip:  使用 NVL function 取代 原有的 OR 陳述式 , 關鍵在於減少重複計算.

Ref:

http://www.dbatools.net/experience/filter_at_index_level.html

 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.