【sql调优之执行计划】merge semi join and merge anti join

版本：10.2.0.4
Semi join（也有叫半连接的）多在子查询in或者exists等中使用，对于外部行集，查找内部（即子查询）行集，匹配第一行之后就返回，不再往下查找例如：
SQL> select b.*
 2   from scott.dept b
 3  where b.deptno in (select deptno from scott.emp a)
 4 ;
 
   DEPTNO DNAME         LOC
---------- -------------- -------------
       10 ACCOUNTING    NEW YORK
       20 RESEARCH      DALLAS
       30 SALES         CHICAGO
 
 
Execution Plan
----------------------------------------------------------
Plan hash value: 1090737117
 
----------------------------------------------------------------------------------------
| Id | Operation                   | Name   | Rows | Bytes | Cost (%CPU)| Time    |
----------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT            |        |    3 |   69 |    6 (17)| 00:00:01 |
|  1 | MERGE JOIN SEMI            |        |    3 |   69 |    6 (17)| 00:00:01 |
|  2 |  TABLE ACCESS BY INDEX ROWID| DEPT   |    4 |   80 |    2  (0)| 00:00:01 |
|  3 |   INDEX FULL SCAN          | PK_DEPT |    4 |      |    1  (0)| 00:00:01 |
|* 4 |  SORT UNIQUE               |        |   14 |   42 |    4 (25)| 00:00:01 |
|  5 |   TABLE ACCESS FULL        | EMP    |   14 |   42 |    3  (0)| 00:00:01 |
----------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
  4 - access("B"."DEPTNO"="DEPTNO")
      filter("B"."DEPTNO"="DEPTNO")
 
 
Statistics
----------------------------------------------------------
         0 recursive calls
         0 db block gets
         7 consistent gets
         0 physical reads
         0 redo size
       614 bytes sent via SQL*Net to client
       400 bytes received via SQL*Net from client
         2 SQL*Net roundtrips to/from client
         1 sorts (memory)
         0 sorts (disk)
         3 rows processed
 
SQL>
或者：
SQL> select b.*
 2   from scott.dept b
 3  where exists (select 1 from scott.emp a where a.deptno = b.deptno)
 4 ;
 
   DEPTNO DNAME         LOC
---------- -------------- -------------
       10 ACCOUNTING    NEW YORK
       20 RESEARCH      DALLAS
       30 SALES         CHICAGO
 
 
Execution Plan
----------------------------------------------------------
Plan hash value: 1090737117
 
----------------------------------------------------------------------------------------
| Id | Operation                   | Name   | Rows | Bytes | Cost (%CPU)| Time    |
----------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT            |        |    3 |   69 |    6 (17)| 00:00:01 |
|  1 | MERGE JOIN SEMI            |        |    3 |   69 |    6 (17)| 00:00:01 |
|  2 |  TABLE ACCESS BY INDEX ROWID| DEPT   |    4 |   80 |    2  (0)| 00:00:01 |
|  3 |   INDEX FULL SCAN          | PK_DEPT |    4 |      |    1  (0)| 00:00:01 |
|* 4 |  SORT UNIQUE               |        |   14 |   42 |    4 (25)| 00:00:01 |
|  5 |   TABLE ACCESS FULL        | EMP    |   14 |   42 |    3  (0)| 00:00:01 |
----------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
  4 - access("A"."DEPTNO"="B"."DEPTNO")
      filter("A"."DEPTNO"="B"."DEPTNO")
 
 
Statistics
----------------------------------------------------------
         1 recursive calls
         0 db block gets
         7 consistent gets
         0 physical reads
         0 redo size
       614 bytes sent via SQL*Net to client
       400 bytes received via SQL*Net from client
         2 SQL*Net roundtrips to/from client
         1 sorts (memory)
         0 sorts (disk)
         3 rows processed
 
SQL>
可以看到这种情况下，in和exsits的执行计划完全相同，且都使用了merge join semi的oporation
而not in或者not exists则不同，Oracle7.3版本之前not exists和not in还使用的tilter，merge anti join和hash anti join访问路径是后来增加的。
例子：
SQL> select b.*
 2   from scott.dept b
 3  where not exists (select 1 from scott.emp a where a.deptno = b.deptno)
 4 ;
 
   DEPTNO DNAME         LOC
---------- -------------- -------------
       40 OPERATIONS    BOSTON
 
 
Execution Plan
----------------------------------------------------------
Plan hash value: 1353548327
 
----------------------------------------------------------------------------------------
| Id | Operation                   | Name   | Rows | Bytes | Cost (%CPU)| Time    |
----------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT            |        |    1 |   23 |    6 (17)| 00:00:01 |
|  1 | MERGE JOIN ANTI            |        |    1 |   23 |    6 (17)| 00:00:01 |
|  2 |  TABLE ACCESS BY INDEX ROWID| DEPT   |    4 |   80 |    2  (0)| 00:00:01 |
|  3 |   INDEX FULL SCAN          | PK_DEPT |    4 |      |    1  (0)| 00:00:01 |
|* 4 |  SORT UNIQUE               |        |   14 |   42 |    4 (25)| 00:00:01 |
|  5 |   TABLE ACCESS FULL        | EMP    |   14 |   42 |    3  (0)| 00:00:01 |
----------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
  4 - access("A"."DEPTNO"="B"."DEPTNO")
      filter("A"."DEPTNO"="B"."DEPTNO")
 
 
Statistics
----------------------------------------------------------
         0 recursive calls
         0 db block gets
         5 consistent gets
         0 physical reads
         0 redo size
       535 bytes sent via SQL*Net to client
       400 bytes received via SQL*Net from client
         2 SQL*Net roundtrips to/from client
         1 sorts (memory)
         0 sorts (disk)
         1 rows processed
这里是merge join anti（也叫反连接），和semi相反，只有外部行在内部不能匹配的时候才返回。
而，not in则和not exsits不同，执行计划显示的是filter：
SQL> select b.*
 2   from scott.dept b
 3  where b.deptno not in (select deptno from scott.emp a)
 4 ;
 
   DEPTNO DNAME         LOC
---------- -------------- -------------
       40 OPERATIONS    BOSTON
 
 
Execution Plan
----------------------------------------------------------
Plan hash value: 3547749009
 
---------------------------------------------------------------------------
| Id | Operation         | Name | Rows | Bytes | Cost (%CPU)| Time    |
---------------------------------------------------------------------------
|  0 | SELECT STATEMENT  |     |    3 |   60 |    7  (0)| 00:00:01 |
|* 1 | FILTER           |     |      |      |           |         |
|  2 |  TABLE ACCESS FULL| DEPT |    4 |   80 |    3  (0)| 00:00:01 |
|* 3 |  TABLE ACCESS FULL| EMP |    2 |    6 |    2  (0)| 00:00:01 |
---------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
  1 - filter( NOT EXISTS (SELECT /*+ */ 0 FROM "SCOTT"."EMP" "A" WHERE
             LNNVL("DEPTNO"<>:B1)))
  3 - filter(LNNVL("DEPTNO"<>:B1))
 
 
Statistics
----------------------------------------------------------
         1 recursive calls
         0 db block gets
        19 consistent gets
         5 physical reads
         0 redo size
       535 bytes sent via SQL*Net to client
       400 bytes received via SQL*Net from client
         2 SQL*Net roundtrips to/from client
         0 sorts (memory)
         0 sorts (disk)
         1 rows processed
 
SQL>
我们知道not in与not exsits并不能等同，从执行计划上来看，not in的执行计划的operation是filter，而且内表和外表都是全表，没有使用索引，而从谓词信息中来看，operation 1为：
1 - filter( NOT EXISTS (SELECT /*+ */ 0 FROM "SCOTT"."EMP" "A" WHERE
             LNNVL("DEPTNO"<>:B1)))
Null值对not in影响较大，如果稍稍修改一下这个查询，则又有不同了：
SQL> select b.*
 2   from scott.dept b
 3  where b.deptno not in (select nvl(deptno,0) from scott.emp a);
 
   DEPTNO DNAME         LOC
---------- -------------- -------------
       40 OPERATIONS    BOSTON
 
 
Execution Plan
----------------------------------------------------------
Plan hash value: 1353548327
 
----------------------------------------------------------------------------------------
| Id | Operation                   | Name   | Rows | Bytes | Cost (%CPU)| Time    |
----------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT            |        |    1 |   23 |    6 (17)| 00:00:01 |
|  1 | MERGE JOIN ANTI            |        |    1 |   23 |    6 (17)| 00:00:01 |
|  2 |  TABLE ACCESS BY INDEX ROWID| DEPT   |    4 |   80 |    2  (0)| 00:00:01 |
|  3 |   INDEX FULL SCAN          | PK_DEPT |    4 |      |    1  (0)| 00:00:01 |
|* 4 |  SORT UNIQUE               |        |   14 |   42 |    4 (25)| 00:00:01 |
|  5 |   TABLE ACCESS FULL        | EMP    |   14 |   42 |    3  (0)| 00:00:01 |
----------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
  4 - access("B"."DEPTNO"=NVL("DEPTNO",0))
      filter("B"."DEPTNO"=NVL("DEPTNO",0))
 
 
Statistics
----------------------------------------------------------
         1 recursive calls
         0 db block gets
         5 consistent gets
         0 physical reads
         0 redo size
       535 bytes sent via SQL*Net to client
       400 bytes received via SQL*Net from client
         2 SQL*Net roundtrips to/from client
         1 sorts (memory)
         0 sorts (disk)
         1 rows processed
 
SQL>
奇怪的是，这里使用了索引，因为告诉了oracle不会有null值，而且谓词信息也发生了改变：
4 - access("B"."DEPTNO"=NVL("DEPTNO",0))
这也是使用not in需要注意的地方。

http://www.itpub.net/viewthread.php?tid=438650&extra=&page=1

转自：http://space.itpub.net/16179598/viewspace-671211