Oracle Hint：USE_NL、USE_MERGE、UESE_HASH

下面內容取自http://yangtingkun.itpub.net/post/468/26696

一、USE_NL(嵌套迴圈串連)

    在嵌套迴圈串連中,Oracle從第一個行源中讀取第一行,然後和第二個行源中的資料進行對比。所有匹配的記錄放在結果集中,然後Oracle將讀取第一個行源中的下一行。按這種方式直至第一個資料來源中的所在行都經過處理。第一個記錄源通常稱為外部表格,或者驅動表,相應的第二個行源稱為內部表。使用嵌套迴圈串連是一種從串連結果中提取第一批記錄的最快速的方法。

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

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

圖1-1說明了程式清單1-1中查詢執行的方法。

select   /*+ordered*/ename,dept.deptno

from      dept,emp

where     dept.deptno=emp.deptno

二、USE_MERGE(排列合并串連)

    在排列合并串連中,Oracle分別將第一個源表、第二個源表按它們各自要串連的列排序,然後將兩個已經排序的源表合并。如果找到匹配的資料,就放到結果集中。

    在缺乏資料的選擇性或者可用的索引時,或者兩個源表都過於龐大(超過記錄數的5%)時,排序合并串連將比嵌套迴圈連更加高效。但是,排列合并串連只能用於等價串連(WHERE D.deptno=E.dejptno,而不是WHERE D.deptno>=E.deptno)。排列合并串連需要臨時的記憶體塊,以用於排序(如果SORT_AREA_SIZE設定得太小的話)。這將導致在暫存資料表空間佔用更多的記憶體和磁碟I/O。

圖1-2解釋了程式清單1-2查詢執行的方法。

select   /*+ordered*/ename,dept.deptno

from      emp,dept

where     dept.deptno=emp.deptno

三、USE_HASH(雜湊串連)

    當記憶體能夠提供足夠的空間時,雜湊(HASH)串連是Oracle最佳化器通常的選擇。在雜湊串連中,Oracle訪問一張表(通常是較大的表),並在記憶體中建立一張基於串連鍵的雜湊表。然後它掃描串連中其他的表（通常是較大的表），並根據雜湊表檢測是否有匹配的記錄。

只有在資料庫初始化參數HASH_JOIN_ENABLED設為True,並且為參數PGA_AGGREGATE_TARGET設定了一個足夠大的值的時候,Oracle才會使用雜湊邊串連(HASH_AREA_SIZE是向下相容的參數,但在Oracle9i之前的版本中應當使用HASH_AREA_SIZE)。這和嵌套迴圈串連有點類似——Oracle先建立一張雜湊表以利於操作進行。當使用ORDERED提示時,FROM子句中的第一張表將用於建立雜湊表。

    當缺少有用的索引時，雜湊串連比嵌套迴圈串連更加有效。雜湊串連可能比排序合并串連更快，因為在這種情況下只有一張源表需要排序。雜湊串連也可能比嵌套迴圈串連更快，因為處理記憶體中的雜湊表比檢索B_樹索引更加迅速。和排序合并串連、群集串連一樣，雜湊串連只能用於等價串連。和排序合并串連一樣，雜湊串連使用記憶體資源，並且當用於排序記憶體不足時，會增加暫存資料表空間的I/O（這將使這種串連方法速度變得極慢）。最後，只有基於代價的最佳化器才可以使用雜湊串連。

圖1-3解釋了執行程式清單1-3查詢的方法。

select   /*+ordered*/ename,dept.deptno

from      emp,dept

where     dept.deptno=emp.deptno