HASH JOIN ,MERGE JOIN ,NESTED LOOP的比較

jackjw發表於2009-03-19

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

[@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 初始化引數必須足夠的大,如果是9iOracle建議使用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。基於CostOracle最佳化器(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

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/3907/viewspace-1019024/,如需轉載,請註明出處,否則將追究法律責任。

相關文章