HASH JOIN ,MERGE JOIN ,NESTED LOOP的比較

都是網上抄的,做一下總結

[@more@]

NESTED LOOP:

對於被連線的資料子集較小的情況，巢狀迴圈連線是個較好的選擇。在巢狀迴圈中，內表被外表驅動，外表返回的每一行都要在內表中檢索找到與它匹配的行，因此整個查詢返回的結果集不能太大（大於1 萬不適合），要把返回子集較小表的作為外表（CBO 預設外表是驅動表），而且在內表的連線欄位上一定要有索引。當然也可以用ORDERED 提示來改變CBO預設的驅動表，使用USE_NL(table_name1 table_name2)可是強制CBO 執行巢狀迴圈連線。

HASH JOIN :

雜湊連線是CBO 做大資料集連線時的常用方式，最佳化器使用兩個表中較小的表（或資料來源）利用連線鍵在記憶體中建立雜湊表，然後掃描較大的表並探測雜湊表，找出與雜湊表匹配的行。

這種方式適用於較小的表完全可以放於記憶體中的情況，這樣總成本就是訪問兩個表的成本之和。但是在表很大的情況下並不能完全放入記憶體，這時最佳化器會將它分割成若干不同的分割槽，不能放入記憶體的部分就把該分割槽寫入磁碟的臨時段，此時要有較大的臨時段從而儘量提高I/O 的效能。

也可以用USE_HASH(table_name1 table_name2)提示來強制使用雜湊連線。如果使用雜湊連線HASH_AREA_SIZE 初始化引數必須足夠的大，如果是9i，Oracle建議使用SQL工作區自動管理，設定WORKAREA_SIZE_POLICY 為AUTO，然後調整PGA_AGGREGATE_TARGET 即可。

MERGE JOIN排序合併連線

通常情況下雜湊連線的效果都比排序合併連線要好，然而如果行源已經被排過序，在執行排序合併連線時不需要再排序了，這時排序合併連線的效能會優於雜湊連線。可以使用USE_MERGE(table_name1 table_name2)來強制使用排序合併連線

幾種方式的操作方式

merge join

merge join的操作通常分三步：

1、對連線的每個表做table access full;

2、對table access full的結果進行排序。

3、進行merge join對排序結果進行合併。

在全表掃描比索引範圍掃描再透過rowid進行表訪問更可取的情況下，merge join會比nested loops效能更佳。當表特別小或特別巨大的時候，實行全表訪問可能會比索引範圍掃描更有效。mrege join的效能開銷幾乎都在前兩步。

hash join

對兩個表進行全表掃描，然後oracle讀取涉及連線的其中一個表，並且在記憶體裡建立來自表的連線列的唯一關鍵字的點陣圖。當讀取和處理第二個表的行時，建立值的點陣圖被用做過濾器。如果一個行成功的透過點陣圖過濾，則hash演算法用於資料查詢和後來的連線。（這裡涉及數學問題，我也弄的不是很清楚）。

以下條件下hash join可能有優勢：

兩個巨大的表之間的連線。

在一個巨大的表和一個小表之間的連線。

Nested Loops

會迴圈外表（驅動表），逐個比對和內表的連線是否符合條件。在驅動表比較小，內表比較大，而且內外表的連線列有索引的時候比較好。當SORT_AREA空間不足的時候，Oracle也會選擇使用NL。基於Cost的Oracle最佳化器(CBO)會自動選擇較小的表做外表。

ordered是你確定了驅動表,而其它表的順序不能確定的情況,將使用cbo

use_nl是使用nest loop連線,適合小表驅動大表,返回結果小的結果最快

use_hash會佔用相當大的系統資源,驅動表（full scan)連線欄位被裝載

到記憶體(hash集合),被驅動表的連線欄位nest loop方式去與驅動表連線

鍵進行匹配.....

.

use_merge通常用在不適合使用use_hash的場合,先對使用use_merge連線的表進行排序,然後合併,速度不如use_hash快,也會用相當大的disk i/o