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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/354732/viewspace-692564/