Oracle Hint：USE_NL、USE_MERGE、UESE_HASH(原理)

   在巢狀迴圈連線中,Oracle從第一個行源中讀取第一行,然後和第二個行源中的資料進行對比。所有匹配的記錄放在結果集中,然後Oracle將讀取第一個行源中的下一行。按這種方式直至第一個資料來源中的所在行都經過處理。第一個記錄源通常稱為外部表,或者驅動表,相應的第二個行源稱為內部表。使用巢狀迴圈連線是一種從連線結果中提取第一批記錄的最快速的方法。
    在驅動行源表(就是您正在查詢的記錄)較小、或者內部行源表已連線的列有惟一的索引或高度可選的非惟一索引時, 巢狀迴圈連線效果是比較理想的。巢狀迴圈連線比其他連線方法有優勢,它可以快速地從結果集中提取第一批記錄,而不用等待整個結果集完全確定下來。這樣,在理想情況下,終端使用者就可以透過查詢螢幕檢視第一批記錄,而在同時讀取其他記錄。不管如何定義連線的條件或者模式,任何兩行記錄源可以使用巢狀迴圈連線,所以巢狀迴圈連線是非常靈活的。然而,如果內部行源表(讀取的第二張表)已連線的列上不包含索引,或者索引不是高度可選時, 巢狀迴圈連線效率是很低的。如果驅動行源表(從驅動表中提取的記錄)非常龐大時,其他的連線方法可能更加有效。
    圖1-1說明了程式清單1-1中查詢執行的方法。


    select 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 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 ename,dept.deptno
    from emp,dept
    where dept.deptno=emp.deptno