ORACLE表串連方式

一、引言

資料倉儲技術是目前已知的比較成熟和被廣泛採用的解決方案，用於整和電信運營企業內部所有分散的原始業務資料，並通過便捷有效資料訪問手段，可以支援企業內部不同部門，不同需求，不同層次的使用者隨時獲得自己所需的資訊。資料倉儲系統需要能夠及時地追蹤和分析大量的曆史資料，並能夠及時做出分析和預測，因此即時性是一個非常重要的指標。ORACLE由於可靠性、高效能等方面的特點，在電信行業大部分的資料倉儲系統中擔當了後台資料庫的角色。由於電信行業的特點，處理的資料量十分龐大，處理的時間長。尤其是對於大表之間的關聯操作，有的大表的記錄數達到數億條，處理時間更是漫長，這成為影響資料庫運行效率的主要因素。因此，對於資料庫的效能最佳化相當重要。效能最佳化是個很大的課題，需要綜合考慮，從伺服器、磁碟、網路、ORACLE執行個體、ORACLE SQL等多方面著手。本文著重分析ORACLE SQL最佳化中對於系統效能影響極大的表串連方式、特點、適用範圍，並對如何使用和最佳化做了詳細的探討。 二、表的串連

表的串連是指在一個SQL語句中通過表與表之間的關聯，從一個或多個表檢索出相關的資料。串連是通過SQL語句中FROM從句的多個表名，以及WHERE從句裡定義的表之間的串連條件來實現的。如果一個SQL語句的關聯表超過兩個，那麼串連的順序如何呢。ORACLE首先串連其中的兩個表，產生一個結果集；然後將產生的結果集與下一個表再進行關聯；繼續這個過程，直到所有的表都串連完成；最後產生所需的資料。

下面都以兩個表的串連為例create table user_info(user_namechar(10),user_id char(10));

create table dev_info(dev_no char(10),user_id char(10),dev_type char(10));

說明和分析表的各種串連方式。

ORACLE 從6的版本開始，最佳化器使用4種不同的表的串連方式：? 嵌套迴圈串連（NESTED LOOP JOIN）

? 群集串連 (CLUSTER JOIN) ? 排序合并串連（SORT MERGE JOIN）

? 笛卡爾串連 (CARTESIAN JOIN) ORACLE 7.3中，新增加了? 雜湊串連(HASH JOIN)。

在ORACLE 8中，新增加了? 索引串連(INDEX JOIN)。

這六種串連方式都有其獨特的技術特點，在一定的條件下，可以充分發揮高效的效能。

但是也都有其局限性，如果使用不當，不僅不能提高效率，反而會嚴重影響系統的效能。因此，深入地探討串連方式的內部運行機制對於效能最佳化是必要的。

1.     嵌套迴圈串連

嵌套迴圈串連的內部處理的流程：

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

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

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

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

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

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

通過查詢SQL語句的執行計畫可以看出哪個表是外部表格，哪個為內部表。

如 select a.user_name,b.dev_no from user_info a, dev_info b where a.user_id= b.user_id;

上面的表是外部表格，即驅動表

下面的表是內部表

的執行計畫:

SELECT STATEMENT Optimizer=CHOOSE

NESTED LOOPS

TABLE ACCESS (FULL) OF 'USER_INFO'

TABLE ACCESS (FULL) OF 'DEV_INFO'

使用嵌套迴圈串連是一種從結果集中提取第一批記錄最快速的方法。在驅動行源表(就是正在尋找的記錄)較小、或者內部行源表已串連的列有惟一的索引或高度可選的非惟一索引時, 嵌套迴圈串連效果是比較理想的。嵌套迴圈串連比其他串連方法有優勢,它可以快速地從結果集中提取第一批記錄,而不用等待整個結果集完全確定下來。這樣,在理想情況下,終端使用者就可以通過查詢螢幕查看第一批記錄,而在同時讀取其他記錄。不管如何定義串連的條件或者模式,任何兩行記錄源可以使用嵌套迴圈串連,所以嵌套迴圈串連是非常靈活的。

然而,如果內部行源表(讀取的第二張表)已串連的列上不包含索引,或者索引不是高度可選時, 嵌套迴圈串連效率是很低的。如果驅動表的記錄非常龐大時,其他的串連方法可能更加有效。

可以通過在SQL語句中添加HINTS,強制ORACLE最佳化器產生嵌套迴圈串連的執行計畫。

select /*+ use_nl(a b) */ a.user_name,b.dev_no from user_info a, dev_infob where a.user_id = b.user_id;

2.     群集串連（CLUSTER JOIN）

群集串連實際上是嵌套迴圈串連的一種特例。如果所串連的兩張源表是群集中的表，即兩張表屬於同一個段（SEGMENT），那麼ORACLE能夠使用群集串連。處理的過程是：ORACLE從第一張行源表中讀取第一行，然後在第二張行源表中使用CLUSTER索引尋找能夠匹配到的紀錄；繼續上面的步驟處理行源表中的第二行，直到所有的記錄全部處理完。

群集串連的效率極高，因為兩個參加串連的行源表實際上處於同一個物理塊上。但是，群集串連也有其限制，沒有群集的兩個表不可能用群集串連。所以，群集串連實際上很少使用。

3.     排序合并串連（SORT MERGE JOIN）

排序合并串連內部處理的流程：

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

2)第一個源表排序

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

4)第二個源表排序

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

在缺乏資料的選擇性或者可用的索引時,或者兩個源表都過於龐大(所選的資料超過表記錄數的5%)時,排序合并串連將比嵌套迴圈連更加高效。

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

select a.user_name,b.dev_no from user_info a, dev_info b where a.user_id> b.user_id;

Plan

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

SELECT STATEMENT Optimizer=CHOOSE (Cost=7 Card=336 Bytes=16128)

MERGE JOIN (Cost=7 Card=336 Bytes=16128)

SORT (JOIN) (Cost=4 Card=82 Bytes=1968)

TABLE ACCESS (FULL) OF 'USER_INFO' (Cost=2 Card=82 Bytes=1968)

SORT (JOIN) (Cost=4 Card=82 Bytes=1968)

TABLE ACCESS (FULL) OF 'DEV_INFO' (Cost=2 Card=82 Bytes=1968)

可以通過在SQL語句中添加HINTS,強制ORACLE最佳化器產生排序合并串連的執行計畫。

select /*+ use_merge(a b) */ a.user_name,b.dev_no from user_info a,dev_info b where a.user_id > b.user_id;

排序合并串連是基於RBO的。

4.     笛卡爾串連(CARTESIAN JOIN)

笛卡爾串連是指在sql語句中沒有寫出表串連的條件，最佳化器把第一個表的每一條記錄和第二個表的所有紀錄相串連。如果第一個表的紀錄數為m, 第二個表的紀錄數為m,則會產生m*n條紀錄數。

下面的查詢，未指名串連條件，就會產生笛卡爾串連。

select a.user_name,b.dev_no from user_info a ,dev_info b;

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

5.     雜湊串連

當記憶體能夠提供足夠的空間時,雜湊(HASH)串連是Oracle最佳化器通常的選擇。雜湊串連中，最佳化器根據統計資訊，首先選擇兩個表中的小表，在記憶體中建立這張表的基於串連鍵的雜湊表；最佳化器再掃描表串連中的大表，將大表中的資料與雜湊表進行比較，如果有相關聯的資料，則將資料添加到結果集中。

當表串連中的小表能夠完全cache到可用記憶體的時候，雜湊串連的效果最佳。雜湊串連的成本只是兩個表從硬碟讀入到記憶體的成本。

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

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

1）第二個大表進行掃描

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

3）大表的第一個分區cache到記憶體

4） 對大表第一個分區的資料進行掃描，並與雜湊表進行比較，如果有匹配的紀錄，添加到結果集裡面

5） 與第一個分區一樣，其它的分區也類似處理。

6） 所有的分區處理完後，ORACLE對產生的結果集進行歸併，匯總，產生最終的結果。

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

當串連的兩個表是用等值串連並且表的資料量比較大時，最佳化器才可能採用雜湊串連。雜湊串連是基於CBO的。只有在資料庫初始化參數HASH_JOIN_ENABLED設為True,並且為參數PGA_AGGREGATE_TARGET設定了一個足夠大的值的時候,Oracle才會使用雜湊邊串連。HASH_AREA_SIZE是向下相容的參數,但在Oracle9i之前的版本中應當使用HASH_AREA_SIZE。當使用ORDERED提示時,FROM子句中的第一張表將用於建立雜湊表。

 

select a.user_name,b.dev_no from user_info a, dev_info b where a.user_id =b.user_id;

Plan

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

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=82 Bytes=3936)

1 0 HASH JOIN (Cost=5 Card=82 Bytes=3936)

2 1 TABLE ACCESS (FULL) OF 'USER_INFO' (Cost=2 Card=82 Bytes=1968)

3 1 TABLE ACCESS (FULL) OF 'DEV_INFO' (Cost=2 Card=82 Bytes=1968)

可以通過在SQL語句中添加HINTS,強制ORACLE最佳化器產生雜湊串連的執行計畫。

select /*+ use_hash(a b)*/ a.user_name,b.dev_no from user_info a, dev_infob where a.user_id = b.user_id;

當缺少有用的索引時，雜湊串連比嵌套迴圈串連更加有效。雜湊串連也可能比嵌套迴圈串連更快，因為處理記憶體中的雜湊表比檢索B_樹索引更加迅速。

6.     索引串連

如果一組已存在的索引包含了查詢所需要的所有資訊，那麼最佳化器將在索引中有選擇地產生一組雜湊表。可通過範圍或者快速全域掃描訪問到每一個索引，而選擇何種掃描方式取決於WHERE子句中的可有條件。在一張表有大量的列，而您只想訪問有限的列時，這種方法非常有效。WHERE子句約束條件越多，執行速度越快。因為最佳化器在評估執行查詢的最佳化路徑時，將把約束條件作為選項看待。您必須在合適的列（那些滿足整個查詢的列）上建立索引，這樣可以確保最佳化器將索引串連作為可選項之一。這個任務通常牽涉到在沒有索引，或者以前沒有建立聯合索引的列上增加索引。相對於快速全域掃描，串連索引的優勢在於：快速全域掃描只有一個單一索引滿足整個查詢;索引串連可以有多個索引滿足整個查詢。

假設表dev_info上有兩個索（一個在dev_no,一個在dev_type 上）。

作如下的查詢

select dev_no,dev_type

from user_info

where user_id =‘U101010’

and dev_type =‘1010’; 三、幾種主要表串連的比較

類別	嵌套迴圈串連	排序合并串連	雜湊串連
最佳化器提示	USE_NL	USE_MERGE	USE_HASH
使用的條件	任何串連	主要用於不等價串連，如<、<=、 >、 >=; 但是不包括 <>	僅用於等價串連
相關資源	CPU、磁碟I/O	記憶體、臨時空間	記憶體、臨時空間
特點	當有高選擇性索引或進行限制性搜尋時效率比較高，能夠快速返回第一次的搜尋結果。	當缺乏索引或者索引條件模糊時，排序合并串連比嵌套迴圈有效。	當缺乏索引或者索引條件模糊時，雜湊串連串連比嵌套迴圈有效。通常比排序合并串連快。 在資料倉儲環境下，如果表的紀錄數多，效率高。
缺點	當索引丟失或者查詢條件限制不夠時，效率很低； 當表的紀錄數多時，效率低。	所有的表都需要排序。它為最佳化的輸送量而設計，並且在結果沒有全部找到前不返回資料。	為建立雜湊表，需要大量記憶體。第一次的結果返回較慢。

四、結束語

深入地理解和掌握oracle的表串連對於最佳化資料庫的效能至關重要。由於最佳化器選擇方式的不同，以及統計資訊的缺失或統計資訊的不準確，ORACLE自動選擇的表串連方式不一定是最優的。當SQL語句的執行效率很低時，可通過auto trace對執行計畫進行跟蹤和分析。當出現多表串連時，需要仔細分析是否有更佳的串連條件。根據系統的特點，必要時可以在SQL中添加HINTS,從而改變SQL的執行計畫，從而達到效能最佳化的目的。