Oracle 在連線條件裡處理和比較 NULL 值

當進行表連線時，希望把 NULL 值對映到一個預設值上，這樣就可以避免使用外連線了。可以用 NVL 函式把待連線表的外來鍵列上的 NULL 值進行轉換。

SQL> select employee_id, first_name, last_name, department_id
  2    from hr.employees
  3   where employee_id = 178;


EMPLOYEE_ID FIRST_NAME           LAST_NAME                 DEPARTMENT_ID
----------- -------------------- ------------------------- -------------
        178 Kimberely            Grant

使用外連線的方式處理

SQL> select employee_id, first_name, last_name, d.department_id, department_name
  2    from hr.employees e
  3    left join hr.departments d
  4      on e.department_id = d.department_id;

EMPLOYEE_ID FIRST_NAME           LAST_NAME                 DEPARTMENT_ID DEPARTMENT_NAME
----------- -------------------- ------------------------- ------------- ------------------------------
        112 Jose Manuel          Urman                               100 Finance
        111 Ismael               Sciarra                             100 Finance
        110 John                 Chen                                100 Finance
        109 Daniel               Faviet                              100 Finance
        108 Nancy                Greenberg                           100 Finance
        206 William              Gietz                               110 Accounting
        205 Shelley              Higgins                             110 Accounting
        178 Kimberely            Grant

以下查詢把所有部門為 NULL 的 EMPLOYEE 轉換到編碼位 110 的部門

SQL> select employee_id, first_name, last_name, d.department_id, department_name
  2    from hr.employees e
  3    join hr.departments d
  4      on nvl(e.department_id, 110) = d.department_id;

EMPLOYEE_ID FIRST_NAME           LAST_NAME                 DEPARTMENT_ID DEPARTMENT_NAME
----------- -------------------- ------------------------- ------------- ------------------------------
        109 Daniel               Faviet                              100 Finance
        110 John                 Chen                                100 Finance
        111 Ismael               Sciarra                             100 Finance
        112 Jose Manuel          Urman                               100 Finance
        113 Luis                 Popp                                100 Finance
        178 Kimberely            Grant                               110 Accounting
        205 Shelley              Higgins                             110 Accounting
        206 William              Gietz                               110 Accounting

在條件連線中，把 NULL 對映到非 NULL 值來避免使用 OUTER JOIN 可能會存在效能方面的問題，因為不會用到 EMPLOYEES.DEPARTMENT_ID 上的索引（主要是因為 NULL 列沒有索引）。我們可以用基於函式的索引來解決這個問題。

建立索引前的執行計劃

SQL> explain plan for select employee_id, first_name, last_name, d.department_id, department_name
  2    from hr.employees e
  3    join hr.departments d
  4      on nvl(e.department_id, 110) = d.department_id;

已解釋。

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1343509718

--------------------------------------------------------------------------------------------
| Id  | Operation                    | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |             |   106 |  4028 |     6  (17)| 00:00:01 |
|   1 |  MERGE JOIN                  |             |   106 |  4028 |     6  (17)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| DEPARTMENTS |    27 |   432 |     2   (0)| 00:00:01 |
|   3 |    INDEX FULL SCAN           | DEPT_ID_PK  |    27 |       |     1   (0)| 00:00:01 |
|*  4 |   SORT JOIN                  |             |   107 |  2354 |     4  (25)| 00:00:01 |
|   5 |    TABLE ACCESS FULL         | EMPLOYEES   |   107 |  2354 |     3   (0)| 00:00:01 |

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("D"."DEPARTMENT_ID"=NVL("E"."DEPARTMENT_ID",110))
       filter("D"."DEPARTMENT_ID"=NVL("E"."DEPARTMENT_ID",110))

建立索引

SQL> create index hr.employees_dpet_fbi on hr.employees(nvl(department_id,110));

索引已建立。

建立索引後的執行計劃

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2904398956

---------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                    |   107 |  5136 |     6   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                |                    |   107 |  5136 |     6   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL          | DEPARTMENTS        |    27 |   432 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS BY INDEX ROWID| EMPLOYEES          |     4 |   128 |     1   (0)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN          | EMPLOYEES_DPET_FBI |     4 |       |     0   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access(NVL("DEPARTMENT_ID",110)="D"."DEPARTMENT_ID")


已選擇16行。