【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

----------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------

| 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

----------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------

| 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

----------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------

| 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

---------------------------------------------------------------------------

---------------------------------------------------------------------------

| 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

----------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------

| 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需要注意的地方。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/16179598/viewspace-671211/，如需轉載，請註明出處，否則將追究法律責任。

【sql調優之執行計劃】merge semi join and merge anti join

相關文章