轉載關於JOIN總結

 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)會自動選擇較小的表做外表。

 

 

連線方式總結：

1)）巢狀迴圈(nest loop):

          對於被連線的資料子集較小的情況，巢狀迴圈連線是較好的選擇。在巢狀迴圈中，外表驅動內表，外表返回的每一行都要在內表中檢索找到它匹配的行，因此整個查詢返回的結果集不能太大（大於10000不合適），要把返回子集較小的表作為外表（驅動表），而且在內表的連線欄位上一定要有索引。

2）雜湊連線(hash join):

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

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

        雜湊連線只能應用於等值連線(如WHERE A.COL3 = B.COL4)、非等值連線(WHERE A.COL3 > B.COL4)、外連線(WHERE A.COL3 = B.COL4(+))。

3）排序合併連線（Sort Merge Join ）

          通常情況下雜湊連線的效果都比排序合併連線要好。然而如果行源已經被排過序，在執行排序合併連線時不需要再排序了，這時排序歸併連線的效能會憂於雜湊連線。