Oracle 在连接条件里处理和比较 NULL 值

朴灿烈づ我的快乐病毒、 2024-04-18 01:36 17阅读 0赞

**当进行表连接时，希望把 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/