11g優化器增加Null aware anti join

         在前面的篇章中介紹了10g升級到11g，優化器增加了Null aware anti join功

能，具體針對這個功能我們進行如下的測試：

1、建立測試表

SQL>create table emp (
                 dept_no        number    not null,
                  sal        number,
                  emp_no        number,
                  padding        varchar2(60),
                 constraint e_pk primary key(emp_no)
                 );

SQL>insert into emp select
                     mod(rownum,6),
                     rownum,
                     rownum,
                     rpad('x',60)
            from   all_objects
             where   rownum <= 20000;

SQL>create table dept (
                    dept_no        number(6),
                     dept_group    number
                       );
SQL>insert into dept values(0, 1);
SQL>insert into dept values(1, 1);
SQL>insert into dept values(2, 1);
SQL>insert into dept values(3, 2);
SQL>insert into dept values(4, 2);
SQL>insert into dept values(5, 2);
SQL>commit;
注意一點，我對dept_no沒有not null約束

2、統計分析（略）

3、10g的執行計劃如下

SQL>select   emp.*  from     emp
           where  emp.dept_no in (
                       select   dept.dept_no  from    dept
                       where    dept.dept_group = 1);
執行計劃如下：
 ----------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name | Rows  | Bytes | Cost (%CPU)  | Time     |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |             |  8119 |   562K |    37   (0          )| 00:00:01 |
|*  1 |  FILTER                           |              |            |              |                          |                   |
|   2 |   TABLE ACCESS FULL| EMP    |  9743 |   675K |    25   (0          )| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| DEPT |     1     |     5       |     2   (0)         | 00:00:01  |
----------------------------------------------------------------------------------------------------------

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

   1 - filter( NOT EXISTS (SELECT /*+ */ 0 FROM "DEPT" "DEPT" WHERE
              "DEPT"."DEPT_GROUP"=2 AND LNNVL("DEPT"."DEPT_NO"<>:B1)))
   3 - filter("DEPT"."DEPT_GROUP"=2 AND LNNVL("DEPT"."DEPT_NO"<>:B1))

        我們看到因為dept_no是nullable，所以呼叫了內部的function，其

selectivity(LNNVL)=5%，導致成本計算成本偏離了實際的值，具體成本計算方法、

分析和解決方法參看前面的文章《Anti-Join中允許關聯column為空值( null),造成

hint失效》。

4、11g版本中解決了anti-join column 空值問題，其執行計劃正常：

 ----------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name | Rows  | Bytes | Cost (%CPU)  | Time     |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |            |  6673 |   508K|    45   (3)| 00:00:01 |
|*  1 |  HASH JOIN RIGHT ANTI NA|            |  6673 |   508K|    45   (3)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL         | DEPT |     3 |    18 |     2   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL          | EMP   | 13345 |   938K|    42   (0)| 00:00:01 |
--------------------------------------------------------------------------------

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

   1 - access("EMP"."DEPT_NO"="DEPT"."DEPT_NO")
   2 - filter("DEPT"."DEPT_GROUP"=2)

其是通過內部隱含引數_optimizer_null_aware_antijoin來控制，我們可以進行如下

測試：

alter session set "_optimizer_null_aware_antijoin"=false;

然後檢視SQL語句的執行計劃與步驟3 10g生成的執行計劃一致。

結論：在11g中，不用再考慮anti-join中column是否是空值了。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/354732/viewspace-692564/，如需轉載，請註明出處，否則將追究法律責任。