”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