Anti-Join中允許關聯column為空值( null),造成hint失效

       ”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/，如需轉載，請註明出處，否則將追究法律責任。