多表連線的三種方式詳解 hash join、merge join、 nested loop
在多表聯合查詢的時候，如果我們檢視它的執行計劃，就會發現裡面有多表之間的連線方式。多表之間的連線有三種方式：Nested Loops，Hash Join 和 Sort Merge Join.具體適用哪種型別的連線取決於
- 當前的最佳化器模式 （ALL_ROWS 和 RULE）
- 取決於表大小
- 取決於連線列是否有索引
- 取決於連線列是否排序
下面來介紹三種不同連線工作方式的不同：
實驗sql
假如有10000個城市，對應於10個國家（此例子僅僅可以解釋join工作的過程）
更換最佳化器，新增索引，會影響下面的執行計劃。
drop table country;CREATE TABLE country ( country_id SMALLINT NOT NULL, country_name VARCHAR(50) NOT NULL);
drop table city;CREATE TABLE city ( city_id VARCHAR(50) NOT NULL, city_name VARCHAR(50) NOT NULL, country_id SMALLINT NOT NULL);beginfor i in 1 .. 10 loopinsert into country values(i,'country'||i);end loop;commit;end;beginfor i in 1 .. 10000 loopinsert into city values(i,'city'||i,ceil(i/1000));end loop;commit;end;
一．HASH JOIN:雜湊連線
Hash join雜湊連線是CBO 做大資料集連線時的常用方式，最佳化器使用兩個表中較小的表（通常是小一點的那個表或資料來源）利用連線鍵（JOIN KEY）在記憶體中建立雜湊表，將列資料儲存到hash列表中，然後掃描較大的表，同樣對JOIN KEY進行HASH後探測雜湊表，找出與雜湊表匹配的行。需要注意的是：如果HASH表太大，無法一次構造在記憶體中，則分成若干個partition，寫入磁碟的temporary segment，則會多一個寫的代價，會降低效率。
這種方式適用於較小的表完全可以放於記憶體中的情況，這樣總成本就是訪問兩個表的成本之和。但是在表很大的情況下並不能完全放入記憶體，這時最佳化器會將它分割成若干不同的分割槽，不能放入記憶體的部分就把該分割槽寫入磁碟的臨時段，此時要有較大的臨時段從而儘量提高I/O 的效能。
可以用USE_HASH(table_name1 table_name2)提示來強制使用雜湊連線。
使用情況：
Hash join在兩個表的資料量差別很大的時候.
二．SORT MERGE JOIN:排序合併連線
Merge Join 是先將關聯表的關聯列各自做排序，然後從各自的排序表中抽取資料，到另一個排序表中做匹配。
因為merge join需要做更多的排序，所以消耗的資源更多。 通常來講，能夠使用merge join的地方，hash join都可以發揮更好的效能,即雜湊連線的效果都比排序合併連線要好。然而如果行源已經被排過序，在執行排序合併連線時不需要再排序了，這時排序合併連線的效能會優於雜湊連線。
可以使用USE_MERGE(table_name1 table_name2)來強制使用排序合併連線.
適用情況：
1.RBO模式
2.不等價關聯(>,<,>=,<=,<>)
3.HASH_JOIN_ENABLED=false
4. 用在沒有索引，並且資料已經排序的情況.
三．NESTED LOOP:巢狀迴圈連線
Nested loops 工作方式是迴圈從一張表中讀取資料(驅動表outer table)，然後訪問另一張表（被查詢表 inner table,通常有索引）。驅動表中的每一行與inner表中的相應記錄JOIN。類似一個巢狀的迴圈。
對於被連線的資料子集較小的情況，巢狀迴圈連線是個較好的選擇。在巢狀迴圈中，內表被外表驅動，外表返回的每一行都要在內表中檢索找到與它匹配的行，因此整個查詢返回的結果集不能太大（大於1 萬不適合），要把返回子集較小表的作為外表（CBO 預設外表是驅動表），而且在內表的連線欄位上一定要有索引。當然也可以用ORDERED 提示來改變CBO預設的驅動表。
使用USE_NL(table_name1 table_name2)可是強制CBO 執行巢狀迴圈連線。
適用情況：
適用於驅動表的記錄集比較小（<10000）而且inner表需要有有效的訪問方法（Index），並且索引選擇性較好的時候.
JOIN的順序很重要，驅動表的記錄集一定要小，返回結果集的響應時間是最快的。
參考
多表連線的三種方式詳解 HASH JOIN MERGE JOIN NESTED LOOP