【sql調優之執行計劃】nested loops join and nested loop join outer

版本：10.2.0.4

Nested loops（巢狀迴圈）看一個例子：

SQL> select b.* from scott.emp a,scott.dept b

2 where a.deptno = b.deptno

3 and a.empno = 7369

4 ;

DEPTNO DNAME LOC

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

20 RESEARCH DALLAS

Execution Plan

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

Plan hash value: 2385808155

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

--------

me |

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

--------

| 0 | SELECT STATEMENT | | 1 | 27 | 2 (0)| 00

:00:01 |

| 1 | NESTED LOOPS | | 1 | 27 | 2 (0)| 00

:00:01 |

| 2 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 7 | 1 (0)| 00

:00:01 |

|* 3 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 0 (0)| 00

:00:01 |

| 4 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 80 | 1 (0)| 00

:00:01 |

|* 5 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| 00

:00:01 |

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

--------

Predicate Information (identified by operation id):

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

3 - access("A"."EMPNO"=7369)

5 - access("A"."DEPTNO"="B"."DEPTNO")

Statistics

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

1 recursive calls

0 db block gets

4 consistent gets

0 physical reads

0 redo size

533 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>

簡單的講就是在表1中每取一行資料，然後從表2中查詢匹配的行，然後再回到表1取下一行，如此迴圈取下去。那麼表1中取得的行越少，表2中查詢行越容易則巢狀迴圈的效率越高。

第一個表也通常稱為驅動表（或者外部表），第二個表通常稱為內部表。

對nested loops join 的選擇或者優化，應該考慮驅動錶行的選擇性和絕對數量大小，並且內部表要能方便的訪問到，例如唯一索引，這種結構很多時候用於一個主資料表和一個關聯表（或者字典表等）的關聯，而關聯表很容易使用與主表關聯的欄位上的索引訪問。

上述的執行計劃可以看出，先訪問表emp，emp作為了驅動表，當然也可以使用dept作為驅動表：

SQL> select /*+ leading(b) use_nl(a,b)*/b.* from scott.dept b,scott.emp a

2 where a.deptno = b.deptno

3 and a.empno = 7369;

DEPTNO DNAME LOC

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

20 RESEARCH DALLAS

Execution Plan

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

Plan hash value: 3431005640

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

-------

e |

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

-------

| 0 | SELECT STATEMENT | | 1 | 27 | 7 (0)| 00:

00:01 |

| 1 | NESTED LOOPS | | 1 | 27 | 7 (0)| 00:

00:01 |

| 2 | TABLE ACCESS FULL | DEPT | 4 | 80 | 3 (0)| 00:

00:01 |

|* 3 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 7 | 1 (0)| 00:

00:01 |

|* 4 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 0 (0)| 00:

00:01 |

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

-------

Predicate Information (identified by operation id):

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

3 - filter("A"."DEPTNO"="B"."DEPTNO")

4 - access("A"."EMPNO"=7369)

Statistics

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

1 recursive calls

0 db block gets

15 consistent gets

5 physical reads

0 redo size

533 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>

上述執行計劃先訪問dept表（全表掃描），然後通過index訪問emp表，可以看到後者的nested loops的cost為7，大於前者的cost2，可以初步判斷後者的效率不如前者，或者也可以簡單的看看2者的邏輯讀數量來判斷一下優劣。具體的效能優化結合例項再述。

擴充套件一下，兩個資料集做nested loops join和兩個表相似。

Nested loops join outer 則是由於做了外連線而產生的，例如：

SQL> select b.* from scott.emp a,scott.dept b

2 where a.deptno = b.deptno(+)

3 and a.empno = 7369;

DEPTNO DNAME LOC

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

20 RESEARCH DALLAS

Execution Plan

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

Plan hash value: 1858280091

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

--------

me |

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

--------

| 0 | SELECT STATEMENT | | 1 | 27 | 2 (0)| 00

:00:01 |

| 1 | NESTED LOOPS OUTER | | 1 | 27 | 2 (0)| 00

:00:01 |

| 2 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 7 | 1 (0)| 00

:00:01 |

|* 3 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 0 (0)| 00

:00:01 |

| 4 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 80 | 1 (0)| 00

:00:01 |

|* 5 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| 00

:00:01 |

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

--------

Predicate Information (identified by operation id):

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

3 - access("A"."EMPNO"=7369)

5 - access("A"."DEPTNO"="B"."DEPTNO"(+))

Statistics

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

1 recursive calls

0 db block gets

4 consistent gets

0 physical reads

0 redo size

533 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>

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

【sql調優之執行計劃】nested loops join and nested loop join outer

相關文章