表連線方式及使用場合

NESTED LOOP 巢狀迴圈連線   
    由兩個for迴圈組成。不管什麼連線，本演算法都可以使用。連線的兩個關係，分別稱之為外層關係和內層關係，把資料塊數大的關係作為外層關係，小的關係作內層關係。分為塊巢狀迴圈連線（簡單點說，就是把已經放在記憶體的兩個關係塊全部連線完成後再進行下一資料庫塊的比較，減少記憶體中資料塊的IO）和索引巢狀迴圈連線（如果內層關係有索引，使用索引代替檔案掃描，如果兩個關係均有索引，一般把元組較少的關係作為外層關係時效果好）兩種。適合於建立自然連線。
    對於被連線的資料子集較小的情況，nested loop連線是個較好的選擇。nested loop就是掃描一個表，每讀到一條記錄，就根據索引去另一個表裡面查詢，沒有索引一般就不會是 nested loops。
一般在nested loop中， 驅動表滿足條件結果集不大，被驅動表的連線欄位要有索引，這樣就走nstedloop。如果驅動表返回記錄太多，就不適合nested loops了。（如果連線欄位沒有索引，則適合走hash join，因為不需要索引。）可用ordered提示來改變CBO預設的驅動表，可用USE_NL(table_name1 table_name2)提示來強制使用nested loop。

HASH JOIN 雜湊連線

   可用於實現自然連線和等值連線。在雜湊連線演算法中，雜湊函式h用於對兩個關係的元組進行劃分。此演算法的基本思想是把這兩個關係的元組按連線屬性劃分為一些有相同雜湊值的元組集合。
   hash join是CBO 做大資料集連線時的常用方式。優化器掃描小表（或資料來源），利用連線鍵（也就是根據連線欄位計算hash 值）在記憶體中建立hash表，然後掃描大表，每讀到一條記錄就來探測hash表一次，找出與hash表匹配的行。
當小表可以全部放入記憶體中，其成本接近全表掃描兩個表的成本之和。如果表很大不能完全放入記憶體，這時優化器會將它分割成若干不同的分割槽，不能放入記憶體的部分就把該分割槽寫入磁碟的臨時段，此時要有較大的臨時段從而儘量提高I/O 的效能。臨時段中的分割槽都需要換進記憶體做hash join。這時候成本接近於全表掃描小表+分割槽數*全表掃描大表的代價和。

    （對以上的過程保持疑問，可能是RDMS的問題，在《資料庫系統概念》一書中，hash join演算法的思想是這樣的：對兩個關係的連線屬性分別作hash，hash函式一定要有較好的隨機性和均勻性，如果關係r的一個元組和關係s的一個元組滿足連線條件，那麼他們在連線屬性 上有相同的值。如該值經雜湊函式對映為i，則關係s的那個元組必在H（ri）中，而關係s的那個元組必在H（si）中。因此，H（ri）中的元組只需與H（si）中的元組作比較，而沒有必要與s的其他任何分割槽作比較。很明顯這種演算法比以上演算法代價小的多。）
    至於兩個表都進行分割槽，其好處是可以使用parallel query，就是多個程式同時對不同的分割槽進行join，然後再合併。但是複雜。
使用hash join時，HASH_AREA_SIZE初始化引數必須足夠的大，如果是9i，Oracle建議使用SQL工作區自動管理，設定WORKAREA_SIZE_POLICY 為AUTO，然後調整PGA_AGGREGATE_TARGET即可。
以下條件下hash join可能有優勢：
兩個巨大的表之間的連線。
在一個巨大的表和一個小表之間的連線。
可用ordered提示來改變CBO預設的驅動表，可用USE_HASH(table_name1 table_name2)提示來強制使用hash join。

SORT-MERGE JOIN 排序歸併連線     可用於建立自然連線和等值連線。在已經排好序的兩個關係上，其中重複的屬性已通過投影去除。歸併連線排序演算法為每個關係分配一個指標，這些指標一開始指向相應關係的第一個元組，隨著演算法的進行，指標遍歷整個關係。這樣對兩個關係各遍歷一次。
    sort merge join的操作通常分三步：對連線的每個表做table access full；對table access full的結果進行排序；進行merge join對排序結果進行合併。sort merge join效能開銷幾乎都在前兩步。一般是在沒有索引的情況下，9i開始已經很少出現了，因為其排序成本高，大多為hash join替代了。
通常情況下hash join的效果都比sort merge join要好，然而如果行源已經被排過序，在執行sort merge join時不需要再排序了，這時sort merge join的效能會優於hash join。
在全表掃描比索引範圍掃描再通過rowid進行表訪問更可取的情況下，sort merge join會比nested loops效能更佳。
可用USE_MERGE(table_name1 table_name2)提示強制使用sort merge join。

一．NESTED LOOP:

1.對於被連線的資料子集較小的情況，巢狀迴圈連線是個較好的選擇

2.使用USE_NL(table_name1 table_name2)可是強制CBO執行巢狀迴圈連線

3.Nested loop一般用在連線的表中有索引，並且索引選擇性較好的時候

4.JOIN的順序很重要，驅動表的記錄集一定要小，返回結果集的響應時間是最快的。

5.Nested loops 工作方式是從一張表中讀取資料，訪問另一張表（通常是索引）來做匹配，nested loops適用的場合是當一個關聯表比較小的時候，效率會更高。

例： select/*+ use_nl( test1, test2) */* from test1, test2 where test1.object_

二．HASH JOIN :

1.雜湊連線是CBO做大資料集連線時的常用方式.

2.也可以用USE_HASH(table_name1 table_name2)提示來強制使用雜湊連線

3.Hash join在兩個表的資料量差別很大的時候.

4.Hash join的工作方式是將一個表（通常是小一點的那個表）做hash運算，將列資料儲存到hash列表中，從另一個表中抽取記錄，做hash運算，到hash列表中找到相應的值，做匹配。

三．SORT MERGE JOIN

1.使用USE_MERGE(table_name1 table_name2)來強制使用排序合併連線.

2.Sort Merge join 用在沒有索引，並且資料已經排序的情況.

3.

步驟：將兩個表排序，然後將兩個表合併。通常情況下，只有在以下情況發生時，才會使用此種JOIN方式：

1.RBO模式

2.不等價關聯(>,=,<=,<>)

3.HASH_JOIN_ENABLED=false

4.資料來源已排序

5.Merge Join 是先將關聯表的關聯列各自做排序，然後從各自的排序表中抽取資料，到另一個排序表中做匹配，因為merge join需要做更多的排序，所以消耗的資源更多。通常來講，能夠使用merge join的地方，hash join都可以發揮更好的效能

類別	NESTED LOOP	SORT MERGE JOIN	HASH JOIN
優化器提示	USE_NL	USE_MERGE	USE_HASH
使用的條件	任何連線	主要用於不等價連線，如<、<=、 >、 >=; 但是不包括 <>	僅用於等價連線
相關資源	CPU、磁碟I/O	記憶體、臨時空間	記憶體、臨時空間
特點	當有高選擇性索引或進行限制性搜尋時效率比較高，能夠快速返回第一次的搜尋結果。	當缺乏索引或者索引條件模糊時，排序合併連線比巢狀迴圈有效。	當缺乏索引或者索引條件模糊時，雜湊連線連線比巢狀迴圈有效。通常比排序合併連線快。 在資料倉儲環境下，如果表的紀錄數多，效率高。
缺點	當索引丟失或者查詢條件限制不夠時，效率很低； 當表的紀錄數多時，效率低。	所有的表都需要排序。它為最優化的吞吐量而設計，並且在結果沒有全部找到前不返回資料。	為建立雜湊表，需要大量記憶體。第一次的結果返回較慢。

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