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行。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26506993/viewspace-2018523/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26506993/viewspace-2018523/