Oracle 表連線

一 Oracle 表連線分類

ORACLE 從 6 的版本開始，最佳化器使用 4 種不同的表的連線方式：

1 ：巢狀迴圈連線（ NESTED LOOP JOIN ）

2 ：群集連線（ CLUSTER JOIN ）

3 ：排序合併連線（ SORT MERGE JOIN ）

4 ：笛卡爾連線（ CARTESIAN JOIN ）

ORACLE 7.3 中新增加了：

5 ：雜湊連線（ HASH JOIN ）

在 ORACLE8 中，新增加了：

6 ：索引連線（ INDEX JOIN ）

二 幾種主要表連線的比較

1 類別：

1) 巢狀迴圈連線；

2 ) 排序合併連線；

3 ) 雜湊連線；

2 最佳化器提示：

1) USE_NL;

2 )USE_MERGE

3 )USE_HASH

3 使用條件：

1) 任何連線；

2) 主要用於不等價連線，如 ,>=, 但是不包括 <>;

3) 僅用於等價連線

4 相關資源：

1) CPU 、磁碟 I/O;

2) 記憶體，臨時空間；

3) 記憶體，臨時空間；

5 特點：

1) 當有高選擇性索引或進行限制性搜尋時效率比較高，能快速返回第一次的搜尋結果；

2) 當缺乏索引或者索引條件模糊時，排序合併連線比巢狀連線更有效；

3) 當缺乏索引或者索引條件模糊時，雜湊連線連線比巢狀迴圈有效。

通常比排序合併連線快；在資料倉儲環境下，如果表的紀錄數多，效率高；

6 缺點：

1) 當索引丟失或者查詢條件限制不夠時，效率很低；當表的紀錄數多時，效率低；

2) 所有的表都需要排序。它為最最佳化的吞吐量而設計，並且在結果沒有全部找到前不返回資料；

3) 為建立雜湊表，需要大量記憶體。第一次的結果返回較慢；

三 表連線詳解

一、 巢狀迴圈連線 (nested loop)

1 巢狀迴圈連線的內部處理的流程：

1 ） Oracle 最佳化器根據基於規則 RBO （ rule based optimizer ）或基於成本 CBO （ cost based optimizer ）的原則，選擇兩個表中的一個作為驅動表，並指定其為外部表。

2 ） Oracle 最佳化器再將另外一個表指定為內部表。

3 ） Oracle 從外部表中讀取第一行 , 然後和內部表中的資料逐一進行對比，所有匹配的記錄放在結果集中。

4 ） Oracle 讀取外部表中的第二行，再和內部表中的資料逐一進行對比，所有匹配的記錄新增到結果集中。

5 ）重複上述步驟，直到外部表中的所有紀錄全部處理完。

6 ）最後產生滿足要求的結果集。

2 巢狀迴圈連線使用場景：

1) 在驅動行源表（就是正在查詢的記錄）較小、或者內部行源表已連線的列有惟一的索引或高度可選的非惟一索引時，巢狀迴圈連線效果是比較理想的。

2) 基於巢狀連線的特點，我們可以想得到，它在兩個關聯表的資料量相差比較大時採用，但整體上資料量都不應該太大。該關聯方式適用於得到小資料量的查詢操作。

3 巢狀迴圈連線優勢：

1) 巢狀迴圈連線可以實現快速響應。

因為排序合併連線需要等到排序完後做合併操作時才能開始返回資料，而雜湊連線則也等到驅動結果集所對應的 HASH TABLE 全部構建完後才能開始返回資料。

它可以快速地從結果集中提取第一批記錄，而不用等待整個結果集完全確定下來。

這樣，在理想情況下，終端使用者就可以透過查詢螢幕檢視第一批記錄，而在同時讀取其他記錄。

2) 不管如何定義連線的條件或者模式，任何兩行記錄源可以使用巢狀迴圈連線，所以巢狀迴圈連線是非常靈活的。

4 巢狀迴圈連線缺陷：

1) 然而，如果內部行源表（讀取的第二張表）已連線的列上不包含索引，或者索引不是高度可選時，巢狀迴圈連線效率是很低的。

2) 如果驅動表的記錄非常龐大時，其他的連線方法可能更加有效。

5 巢狀迴圈連線有以下特性 :

(1) 通常 sql 語句中驅動表只訪問一次 , 被驅動表訪問多次

(2) 不必等待處理完成所有行前可以先返回部分已經處理完成的資料

(3) 在限制條件以及連線條件列上建立索引 , 能夠提高執行效率

(4) 支援所有型別的連線 ( 等值連線 , 非等值連線 , like 等 )

6 nested loop 最佳化

1) 首先，要確保結果集小的表為驅動表，結果集多的表為被驅動表。

這不意味著記錄多的表不能作為驅動表 , 只要透過謂詞條件過濾後得到的結果集比較小，也可以作為驅動表。

2) 其次，在驅動表的謂詞條件列以及被驅動表的連線列上加上索引，能夠顯著的提高執行效能。

3) 最後，如果要查詢的列都在索引中，避免回表查詢列資訊時，又將進一步提高執行效能。

7 強制巢狀連線，指定驅動表

select/*+use_nl(dept,emp)*/empno,ename,dname from emp,dept where emp.deptno=dept.deptno;

select/*+use_nl(emp,dept)*/empno,ename,dname from emp,dept where emp.deptno=dept.deptno;

select/*+use_nl(emp,dept)*/empno,ename,dname from dept,emp where emp.deptno=dept.deptno;

SQL> select /*+use_nl(T1,T2)*/ empno,ename,dname from T1,T2 where T1.deptno=T2.deptno;

SQL> select /*+ leading(T1) use_nl(T2)*/ empno,ename,dname from T1,T2 where T1.deptno=T2.deptno;

SQL> select /*+ leading(T2) use_nl(T1)*/ empno,ename,dname from T1,T2 where T1.deptno=T2.deptno;

上面我使用的 /*+ leading(t1) use_nl(t2)*/ 這個 HINT 的含義， USE_NL 表示強制 ORACLE 的最佳化器使用巢狀迴圈的連結方式， leading(t1) 表示 T1 作為驅動表。

8 autotrace

SQL> show user

USER 為 "SCOTT"

SQL> set autotrace on

SP2-0618: 無法找到會話識別符號。啟用檢查 PLUSTRACE 角色

SP2-0611: 啟用 STATISTICS 報告時出錯

SQL> @$ORACLE_HOME/sqlplus/admin/plustrce.sql

SQL> grant plustrace to public;

授權成功。

SQL> conn scott/tiger

已連線。

SQL> set autotrace on

SQL> set linesize 200

SQL> select empno,ename,dname from dept,emp where dept.deptno=emp.deptno;

......

執行計劃

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

Plan hash value: 351108634

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

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

| 0 | SELECT STATEMENT | | 14 | 364 | 4 (0)| 00:00:01 |

| 1 | NESTED LOOPS | | 14 | 364 | 4 (0)| 00:00:01 |

| 2 | TABLE ACCESS FULL | EMP | 14 | 182 | 3 (0)| 00:00:01 |

| 3 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 13 | 1 (0)| 00:00:01 |

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

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

Predicate Information (identified by operation id):

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

4 - access("DEPT"."DEPTNO"="EMP"."DEPTNO")

統計資訊

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

0 recursive calls

0 db block gets

24 consistent gets

0 physical reads

0 redo size

849 bytes sent via SQL*Net to client

385 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

14 rows processed

SQL> create table t1 as select * from emp;

表已建立。

SQL> create table t2 as select * from dept;

表已建立。

t1,t2 無索引和主、外來鍵

SQL> select empno,ename,dname from t1,t2 where t1.deptno=t2.deptno;

.....

執行計劃

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

Plan hash value: 2959412835

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

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

| 0 | SELECT STATEMENT | | 14 | 770 | 7 (15)| 00:00:01 |

|* 1 | HASH JOIN | | 14 | 770 | 7 (15)| 00:00:01 |

| 2 | TABLE ACCESS FULL| T2 | 4 | 88 | 3 (0)| 00:00:01 |

| 3 | TABLE ACCESS FULL| T1 | 14 | 462 | 3 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

1 - access("T1"."DEPTNO"="T2"."DEPTNO")

Note

-----

- dynamic sampling used for this statement

統計資訊

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

112 recursive calls

0 db block gets

25 consistent gets

2 physical reads

0 redo size

849 bytes sent via SQL*Net to client

385 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

2 sorts (memory)

0 sorts (disk)

14 rows processed

二、 群集連線（ CLUSTER JOIN ）

1 群集連線內部處理的流程：

(1)ORACLE 從第一張行源表中讀取第一行；

(2) 然後在第二張行源表中使用 CLUSTER 索引查詢能夠匹配到的紀錄；

(3) 繼續上面的步驟處理行源表中的第二行，直到所有的記錄全部處理完。

2 群集連線：

群集連線實際上是巢狀迴圈連線的一種特例。

3 群集連線使用場景：

如果所連線的兩張源表是群集中的表，即兩張表屬於同一個段（ SEGMENT ），那麼 ORACLE 能夠使用群集連線。

4 群集連線優勢：

群集連線的效率極高，因為兩個參加連線的行源表實際上處於同一個物理塊上。

5 群集連線缺陷：

群集連線也有其限制，沒有群集的兩個表不可能用群集連線。所以，群集連線實際上很少使用。

三、排序合併連線（ SORT MERGE JOIN ）

1 排序合併連線內部處理的流程：

1 ）最佳化器判斷第一個源表是否已經排序，如果已經排序，則到第 3 步，否則到第 2 步。

2 ）第一個源表排序

3 ）最佳化器判斷第二個源表是否已經排序，如果已經排序，則到第 5 步，否則到第 4 步。

4 ）第二個源表排序

5 ）已經排過序的兩個源表進行合併操作，並生成最終的結果集。

2 排序合併連線 (Merge Sort Join) 特點：

1) 驅動表和被驅動表都是最多隻被訪問一次。

2) 排序合併連線的表無驅動順序。

3) 排序合併連線的表需要排序，用到 SORT_AREA_SIZE 。

4) 排序合併連線不適用於的連線條件是：不等於 <> ， like, 其中大於 > ，小於 < ，大於等於 >= ，小於等於 <= ，是可以適用於排序合併連線

5) 排序合併連線，如果有索引就可以排除排序。

3 排序合併連線 (Merge Sort Join) 優點：

在缺乏資料的選擇性或者可用的索引時，或者兩個源表都過於龐大（所選的資料超過表記錄數的 5% ）時，排序合併連線將比巢狀迴圈連更加高效。

4 排序合併連線 (Merge Sort Join) 缺點：

排列合併連線需要比較大的臨時記憶體塊，以用於排序，這將導致在臨時表空間佔用更多的記憶體和磁碟 I/O 。

Merge Sort Join 是一種古老經典的排序模型，類似於資料結構時代的合併排序演算法。

Merge Sort Join 引入的最大優勢是避免同Nest Loop Join類似的大量隨機讀現象，但是同時也引入了 Sort 空間變化的問題。

隨著海量資料處理場景的增多， Merge Sort Join 暴露出缺陷的機會越來越多。

而 Nest Loop Join 的大量隨機讀問題，也是可以透過索引等常規手段加以最佳化。

5 hint

SQL> select /*+ ordered use_merge(t1,t2)*/ empno,ename,dname from t1,t2 where t1.deptno=t2.deptno;

SQL> select /*+ use_merge(t1,t2)*/ empno,ename,dname from t1,t2 where t1.deptno=t2.deptno;

.......

執行計劃

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

Plan hash value: 1792967693

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

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

| 0 | SELECT STATEMENT | | 14 | 770 | 8 (25)| 00:00:01 |

| 1 | MERGE JOIN | | 14 | 770 | 8 (25)| 00:00:01 |

| 2 | SORT JOIN | | 4 | 88 | 4 (25)| 00:00:01 |

| 3 | TABLE ACCESS FULL| T2 | 4 | 88 | 3 (0)| 00:00:01 |

|* 4 | SORT JOIN | | 14 | 462 | 4 (25)| 00:00:01 |

| 5 | TABLE ACCESS FULL| T1 | 14 | 462 | 3 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

4 - access("T1"."DEPTNO"="T2"."DEPTNO")

filter("T1"."DEPTNO"="T2"."DEPTNO")

Note

-----

- dynamic sampling used for this statement

統計資訊

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

7 recursive calls

0 db block gets

14 consistent gets

0 physical reads

0 redo size

773 bytes sent via SQL*Net to client

385 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

4 sorts (memory)

0 sorts (disk)

14 rows processed

四、笛卡爾連線（ CARTESIAN JOIN ）

1 笛卡爾連線內部處理的流程：

笛卡爾連線是指在 sql 語句中沒有寫出表連線的條件，最佳化器把第一個表的每一條記錄和第二個表的所有紀錄相連線。

2 笛卡爾連線特點：

如果第一個表的紀錄數為 m, 第二個表的紀錄數為 n, 則會產生 m*n 條紀錄數。

由於笛卡爾連線會導致效能很差的 SQL ，因此一般也很少用到。

SQL> select empno,ename,dname from emp,dept;

......

已選擇 56 行。

執行計劃

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

Plan hash value: 2034389985

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

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

| 0 | SELECT STATEMENT | | 56 | 1120 | 9 (0)| 00:00:01 |

| 1 | MERGE JOIN CARTESIAN | | 56 | 1120 | 9 (0)| 00:00:01 |

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

| 3 | BUFFER SORT | | 14 | 140 | 6 (0)| 00:00:01 |

| 4 | TABLE ACCESS FULL | EMP | 14 | 140 | 2 (0)| 00:00:01 |

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

統計資訊

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

288 recursive calls

0 db block gets

83 consistent gets

0 physical reads

0 redo size

1792 bytes sent via SQL*Net to client

418 bytes received via SQL*Net from client

5 SQL*Net roundtrips to/from client

11 sorts (memory)

0 sorts (disk)

56 rows processed

SQL> select power(14,5) from dual;

POWER(14,5)

-----------

537824

五、雜湊連線 (HASH JOIN)

雜湊連線（ HASH JOIN ）是一種兩個表在做表連線時主要依靠雜湊運算來得到連線結果集的表連線方法。

1 雜湊連線內部處理的流程：

1) 雜湊連線中，最佳化器根據統計資訊，首先選擇兩個表中的小表，在記憶體中建立這張表的基於連線鍵的雜湊表；

2) 最佳化器再掃描表連線中的大表，將大表中的資料與雜湊表進行比較，如果有相關聯的資料，則將資料新增到結果集中。

當雜湊表構建完成後，進行下面的處理：

3 ）第二個大表進行掃描

4 ）如果大表不能完全 cache 到可用記憶體的時候，大表同樣會分成很多分割槽

5 ）大表的第一個分割槽 cache 到記憶體

6 ）對大表第一個分割槽的資料進行掃描，並與雜湊表進行比較，如果有匹配的紀錄，新增到結果集裡面

7 ）與第一個分割槽一樣，其它的分割槽也類似處理。

8 ）所有的分割槽處理完後， ORACLE 對產生的結果集進行歸併，彙總，產生最終的結果。

2 雜湊連線適用場景：

1) 當記憶體能夠提供足夠的空間時，雜湊（ HASH ）連線是 Oracle 最佳化器通常的選擇。

2) 當表連線中的小表能夠完全 cache 到可用記憶體的時候，雜湊連線的效果最佳。

3) 雜湊連線的驅動表所對應的連線列的選擇性儘可能好。

4) 雜湊只能用於 CBO ，而且只能用於等值連線的條件。（即使是雜湊反連線， ORACLE 實際上也是將其換成等值連線）。

5) 雜湊連線很適用小表和大表之間做連線且連線結果集的記錄數較多的情形，特別是小表的選擇性非常好的情況下，這個時候雜湊連線的執行時間就可以近似看做和全表掃描個個大表的費用時間相當。

6) 當兩個雜湊連線的時候，如果在施加了目標 SQL 中指定的謂詞條件後得到的資料量較小的那個結果集所對應的 HASH TABLE 能夠完全被容納在記憶體中（ PGA 的工作區），此時的雜湊連線的執行效率非常高。

3 雜湊連線 (Hash Join) 特點：

1) 驅動表和被驅動表都是最多隻被訪問一次。

2) 雜湊連線的表有驅動順序。

3) 雜湊表連線的表無需要排序，但是他在做連線之前做雜湊運算的時候，會用到 HASH_AREA_SIZE 來建立雜湊表。

4) 雜湊連線不適用於的連線條件是：不等於 <> ，大於 > ，小於 < ，小於等於 <= ，大於等於 >= ， like 。

5) 雜湊連線索引列在表連線中無特殊要求，與單表情況無異。

6) 只有在資料庫初始化引數 HASH_JOIN_ENABLED 設為 True, 並且為引數 PGA_AGGREGATE_TARGET 設定了一個足夠大的值的時候 ,Oracle 才會使用雜湊連線。

7)HASH_AREA_SIZE 是向下相容的引數 , 但在 Oracle9i 之前的版本中應當使用 HASH_AREA_SIZE 。當使用 ORDERED 提示時 ,FROM 子句中的第一張表將用於建立雜湊表。

4 雜湊連線的成本 :

只是兩個表從硬碟讀入到記憶體的成本。

5 雜湊連線缺陷：

如果雜湊表過大而不能全部 cache 到可用記憶體時，最佳化器將會把雜湊表分成多個分割槽，再將分割槽逐一 cache 到記憶體中。當表的分割槽超過了可用記憶體時，分割槽的部分資料就會臨時地寫到磁碟上的臨時表空間上。因此，分割槽的資料寫磁碟時，比較大的區間（ EXTENT ）會提高 I/O 效能。 ORACLE 推薦的臨時表空間的區間是 1MB 。臨時表空間的區間大小由 UNIFORMSIZE 指定。

當雜湊表過大或可用記憶體有限，雜湊表不能完全 CACHE 到記憶體。隨著滿足連線條件的結果集的增加，可用記憶體會隨之下降，這時已經 CACHE 到記憶體的資料可能會重新寫回到硬碟去。如果出現這種情況，系統的效能就會下降。

6 雜湊連線的由來：

對於排序合併連線，如果兩個表在施加了目標 SQL 中指定的謂詞條件後得到的結果集很大而且需要排序，則排序合併連線的執行效率一定不高；

而對於巢狀迴圈連線，如果驅動表所對應的驅動結果集的記錄數很大，即便在被驅動表的連線列上存在索引，此時使用巢狀迴圈連線的執行效率也會同樣不高。

為了解決這個問題，於是 ORACLE 引進了雜湊連線。

7 hint

SQL> select /*+ ordered use_hash(emp,dept)*/ empno,ename,dname from emp,dept where emp.deptno=dept.deptno;

SQL> select /*+ use_hash(emp,dept)*/ empno,ename,dname from emp,dept where emp.deptno=dept.deptno;

SQL> select /*+ leading(emp) use_hash(dept)*/ empno,ename,dname from emp,dept where emp.deptno=dept.deptno;

SQL> select /*+ leading(dept) use_hash(emp)*/ empno,ename,dname from emp,dept where emp.deptno=dept.deptno;

/*

---nested loop---

SQL> select /*+ leading(dept) use_hash(emp)*/ empno,ename,dname from emp t1,dept t2 where t1.deptno=t2.deptno;

*/

六、索引連線 (INDEX JOIN)

如果一組已存在的索引包含了查詢所需要的所有資訊，那麼最佳化器將在索引中有選擇地生成一組雜湊表。

可透過範圍或者快速全域性掃描訪問到每一個索引，而選擇何種掃描方式取決於 WHERE 子句中的可有條件。

在一張表有大量的列，而您只想訪問有限的列時，這種方法非常有效。 WHERE 子句約束條件越多，執行速度越快。

因為最佳化器在評估執行查詢的最佳化路徑時，將把約束條件作為選項看待。

您必須在合適的列（那些滿足整個查詢的列）上建立索引，這樣可以確保最佳化器將索引連線作為可選項之一。

這個任務通常牽涉到在沒有索引，或者以前沒有建立聯合索引的列上增加索引。

索引連線優勢：

相對於快速全域性掃描，連線索引的優勢在於：快速全域性掃描只有一個單一索引滿足整個查詢 ; 索引連線可以有多個索引滿足整個查詢。

參考： http://lipengy.blog.51cto.com/2769396/1308702

歡迎關注我的微信公眾號"IT小Chen"，共同學習，共同成長！！！

Oracle 表連線

Oracle 表連線

相關文章