關於迴圈巢狀nested loops的一點分析

nested loops連線 /*+use_nl(tab01 tab02)*/

迴圈巢狀連線：oracle最佳化器根據基於rbo和cbo原則，選擇兩個表中一個為驅動表，也就是外部表，另一個作為內部表。注意hint寫法中表如果用的別名，hint中必須寫相應的別名，不然相應的hint不能生效。

在oracle9I後最佳化器推出兩種執行方式，方式1先從外部表取第一行然後和內部表資料進行比較，此時會逐行取出資料，重複上述步驟直到外部表中所有記錄全部處理完畢。

SQL> desc tab01;

Name Type Nullable Default Comments

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

ID NUMBER Y

NAME VARCHAR2(10) Y

SQL> desc tab02;

Name Type Nullable Default Comments

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

ID NUMBER Y

NAME VARCHAR2(10) Y

SQL> explain plan for select a.id,b.name from tab01 a,tab02 b

2 where a.id=b.id;

Explained

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT

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

Plan hash value: 3710181165

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

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time

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

| 0 | SELECT STATEMENT | | 100 | 3300 | 2 (0)| 00:00:01

| 1 | NESTED LOOPS | | 100 | 3300 | 2 (0)| 00:00:01

| 2 | TABLE ACCESS FULL| TAB02 | 100 | 2000 | 2 (0)| 00:00:01

|* 3 | INDEX RANGE SCAN | INDEX_TAB01 | 1 | 13 | 0 (0)| 00:00:01

方式2 也是先從外部表取第一行然後和內部表資料進行比較，此時會記錄內部表上相應的rowid，但是不會馬上去內部表上取出資料，此時會繼續從外部表繼續取第二行資料然後獲取rowid，直到所有資料獲取完畢後，再對內部表進行索引掃描。

SQL> explain plan for select a.id,a.name,b.name from tab01 a,tab02 b where a.id=b.id

;

2 /

Explained

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT

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

Plan hash value: 1970217900

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

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|

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

| 0 | SELECT STATEMENT | | 100 | 4000 | 3 (0)|

| 1 | TABLE ACCESS BY INDEX ROWID| TAB02 | 1 | 20 | 1 (0)|

| 2 | NESTED LOOPS | | 100 | 4000 | 3 (0)|

| 3 | TABLE ACCESS FULL | TAB01 | 100 | 2000 | 2 (0)|

|* 4 | INDEX RANGE SCAN | INDEX_TAB02 | 1 | | 0 (0)|

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

上述sql語句中cbo下oracle採取了nested loops迴圈巢狀，用tab01作為內部表，tab02作為外部表，注意執行計劃中選擇的是下面的表作為內部表，上面的為外部表，也就是驅動表。

由於外部表資料量比較小或者內部表上存在高效索引時，迴圈巢狀是比較高效的，迴圈巢狀連線能最快速地從結果集中提取第一批資料.其實rownum使用時，會產生一個count stopkey的計劃行，解釋為掃描表到前n行停止，當然這也可以當成很快的取出前一批資料的case。不過和nl意義卻大不相同！

[@more@]