一次针对Not in的优化--如何让其选择hash join anti

yyp2009

嗯

not in 和not exitst在处理的差异主要是处理null上

filte其实体现了些

不过如ding所说

这个差异改进和优化在11g中实现了

yyp2009

关键是myownstars 掘的比较深

呵呵

sundog315

排版有点乱，建议修改一下，看着更方便，楼主的注释应该高亮。

secooler

这个帖子的版式之所以乱是因为执行计划中的分割线过长。
我尝试对主帖进行了调整。

建议大家以后贴执行计划时选择“宋体”，这样执行计划将更具可读性。

secooler

myownstars

已对板式进行调整 看起来舒服了一点

yufeng0471

不错

finalarrow

marked and practise it later. thanks LZ

Coast_lichao

Query1：select department_name
        from hr.departments dept
        where department_id NOT IN
                   (select department_id from hr.employees emp);
---------------------------------------------------------------------------------------------
| Id  | Operation                                     | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |                       |    16 |   304 |     6  (17)| 00:00:01 |
|   1 |  MERGE JOIN ANTI NA                    |                        |    16 |   304 |     6  (17)| 00:00:01 |
|   2 |   SORT JOIN                                   |                        |    27 |   432 |     2   (0)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| DEPARTMENTS |    27 |   432 |     2   (0)| 00:00:01 |
|   4 |     INDEX FULL SCAN                      | DEPT_ID_PK   |    27 |           |     1   (0)| 00:00:01 |
|*  5 |   SORT UNIQUE                             |                       |   107 |   321 |     4  (25)| 00:00:01 |
|   6 |    TABLE ACCESS FULL                   | EMPLOYEES    |   107 |   321 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

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

   5 - access("DEPARTMENT_ID"="DEPARTMENT_ID")
       filter("DEPARTMENT_ID"="DEPARTMENT_ID")
no rows selected

Query2:select department_name
       from hr.departments dept
       where NOT EXISTS (select null from hr.employees emp
       where emp.department_id = dept.department_id);
----------------------------------------------------------------------------------------
| Id  | Operation                   | Name                          | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                                    |    16 |   304 |     3   (0)| 00:00:01 |
|   1 |  NESTED LOOPS ANTI  |                                     |    16 |   304 |     3   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL  | DEPARTMENTS             |    27 |   432 |     3   (0)| 00:00:01 |
|*  3 |   INDEX RANGE SCAN  | EMP_DEPARTMENT_IX |    44 |   132 |     0   (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("EMP"."DEPARTMENT_ID"="DEPT"."DEPARTMENT_ID")

16 rows selected.

select count(*) from employees where department_id is null;

  COUNT(*)
----------
         1

11g里面因为employments的column department_id有null值，所以not in返回错误结果。
添加is not null后问题解决。
Query3: select department_name
        from hr.departments dept
        where department_id NOT IN (select department_id from hr.employees emp
        where department_id is not null);
----------------------------------------------------------------------------------------
| Id  | Operation                     | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                                   |    16 |   304 |     3   (0)| 00:00:01 |
|   1 |  NESTED LOOPS ANTI  |                                    |    16 |   304 |     3   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL | DEPARTMENTS             |    27 |   432 |     3   (0)| 00:00:01 |
|*  3 |   INDEX RANGE SCAN | EMP_DEPARTMENT_IX |    43 |   129 |     0   (0)| 00:00:01 |
----------------------------------------------------------------------------------------
16 rows selected.


Query4:select /* NOT IN */ department_name
        from hr.departments dept
        where NVL(department_id,'NULL') NOT IN
        (select department_id from hr.employees emp);

no rows selected
--------------------------------------------------------------------------------------------
| Id  | Operation              | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |                   |     1 |    19 |     6   (0)| 00:00:01 |
|*  1 |  FILTER                |                   |       |       |            |          |
|   2 |   NESTED LOOPS ANTI SNA|                   |    25 |   475 |     6  (50)| 00:00:01 |
|   3 |    TABLE ACCESS FULL   | DEPARTMENTS       |    27 |   432 |     3   (0)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN    | EMP_DEPARTMENT_IX |    10 |    30 |     0   (0)| 00:00:01 |
|*  5 |   TABLE ACCESS FULL    | EMPLOYEES         |     1 |     6 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

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

   1 - filter( NOT EXISTS (SELECT 0 FROM "HR"."EMPLOYEES" "EMP" WHERE
              "DEPARTMENT_ID" IS NULL))
   4 - access("DEPARTMENT_ID"=NVL("DEPARTMENT_ID",TO_NUMBER('NULL')))
   5 - filter("DEPARTMENT_ID" IS NULL)

Query1和Query4都是没有结果返回。其实这样的情况下：
1,为该列加not null约束
2,改写sql,如not exists
3,where 列添加is not null

[ 本帖最后由 Coast_lichao 于 2011-8-29 16:38 编辑 ]

lbs1988

原帖由 dingjun123 于 2011-5-20 21:34 发表
11g有null aware anti join了，不过还是anti join最好

呵呵 兔子哥 null aware anti join 在11gR1 上有BUG 导致ORA-600 ，赞同使用anti join
其实not null的确是个问题，有时候有not null 就是不走索引

边-城

到了11g这两种写法执行计划应该一样了，都是hash join anti，无论连接列是否设为not null