Anti-Join中允许关联column为空值( null),造成hint失效

最新推荐文章于 2022-07-29 15:52:33 发布

comnp66042

最新推荐文章于 2022-07-29 15:52:33 发布

阅读量217

点赞数

       ”Join类型名词"中介绍Anti-Join主要是Not in或Not exists格式，以下实验是验证关联column允许空值(null)的时候，可能造成hint不能生效。

具体实验步骤如下：

1、创建EMP和DEPT表，其中dept_no允许空值

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

SQL>create table dept (
      dept_no        number(6),
      dept_group    number
                );

2、插入数据

SQL>insert into emp
              select
      mod(rownum,6),
                      rownum,
                      rownum,
                      rpad('x',60)
               from
                      all_objects
               where
                       rownum <= 20000;
SQL>insert into dept values(0, 1);
          insert into dept values(1, 1);
          insert into dept values(2, 1);
          insert into dept values(3, 2);
          insert into dept values(4, 2);
          insert into dept values(5, 2);
SQL>commit;

3、统计分析(略）

4、查看缺省执行计划

SQL>set autotrace traceonly explain
SQL>select
      emp.*
          from
               emp
          where
      emp.dept_no not in (
       select
           dept.dept_no
        from    dept
       where    dept.dept_group = 2
                           );
执行计划1：
--------------------------------------------------------------------------------------
| Id | Operation            | Name | Rows | Bytes    | Cost |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |      | 16667 | 1171K|   34 |
|* 1 | FILTER           |              |         |              |         |
|   2 |   TABLE ACCESS FULL    | EMP    | 20000 | 1406K |      22 |
|* 3 |   TABLE ACCESS FULL   | DEPT |       1 |            5 |        2 |
--------------------------------------------------------------------------------------
5、无论在语句中添加如nl_aj，merge_aj，hash_aj等hint，所有hint都不能生效，执行计划不变，与执行计划1一样。
SQL>select
      emp.*
          from
                emp
          where
               emp.dept_no not in (
                       select    /*+ hash_aj */
                          dept.dept_no
                       from    dept
                       where    dept.dept_group = 2
                           );

6、对表EMP和DEPT中的dept_no增加非空约束。
SQL>alter table dept modify dept_no not null;
SQL>alter table emp modify dept_no not null;

7、重新执行上述失效语句，hint生效

--------------------------------------------------------------------------------------
| Id | Operation                             | Name | Rows | Bytes | Cost |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |       |   3333 |   250K|      25 |
|* 1 | HASH JOIN RIGHT ANTI   |      |    3333 |   250K|      25 |
|* 2 |   TABLE ACCESS FULL | DEPT |       6 |   30 |    2 |
|   3 |   TABLE ACCESS FULL      | EMP | 20000 | 1406K|    22 |
---------------------------------------------------------------------------------------

8、或者在语句中添加 dept_no is not null，使得hint生效，执行计划如执行计划2

SQL>alter table dept modify dept_no null;
SQL>alter table emp modify dept_no null;
SQL>select
emp.*
           from
   emp
           where
      emp.dept_no in (
                        select    /*+ hash_sj */
                             dept.dept_no
           from    dept
                         where    dept.dept_group = 1
                         and dept.dept_no is not null
)
             and emp.dept_no is not null;

注：实验素材来自于Jonathan Lewis

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

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