Oracle 表三種連接方式使用介紹(sql優(yōu)化)

1. NESTED LOOP

對(duì)于被連接的數(shù)據(jù)子集較小的情況，nested loop連接是個(gè)較好的選擇。nested loop就是掃描一個(gè)表，每讀到一條記錄，就根據(jù)索引去另一個(gè)表里面查找，沒(méi)有索引一般就不會(huì)是 nested loops。一般在nested loop中， 驅(qū)動(dòng)表滿足條件結(jié)果集不大，被驅(qū)動(dòng)表的連接字段要有索引，這樣就走nstedloop。如果驅(qū)動(dòng)表返回記錄太多，就不適合nested loops了。如果連接字段沒(méi)有索引，則適合走h(yuǎn)ash join，因?yàn)椴恍枰饕?/p>

可用ordered提示來(lái)改變CBO默認(rèn)的驅(qū)動(dòng)表，可用USE_NL(table_name1 table_name2)提示來(lái)強(qiáng)制使用nested loop。

要點(diǎn)如下：

1）對(duì)于被連接的數(shù)據(jù)子集較小的情況，嵌套循環(huán)連接是個(gè)較好的選擇
2）使用USE_NL(table_name1 table_name2)可是強(qiáng)制CBO 執(zhí)行嵌套循環(huán)連接
3）Nested loop一般用在連接的表中有索引，并且索引選擇性較好的時(shí)候
4）OIN的順序很重要，驅(qū)動(dòng)表的記錄集一定要小，返回結(jié)果集的響應(yīng)時(shí)間是最快的。
5）Nested loops 工作方式是從一張表中讀取數(shù)據(jù)，訪問(wèn)另一張表（通常是索引）來(lái)做匹配，nested loops適用的場(chǎng)合是當(dāng)一個(gè)關(guān)聯(lián)表比較小的時(shí)候，效率會(huì)更高。

2. HASH JOIN

hash join是CBO 做大數(shù)據(jù)集連接時(shí)的常用方式。優(yōu)化器掃描小表（數(shù)據(jù)源），利用連接鍵（也就是根據(jù)連接字段計(jì)算hash 值）在內(nèi)存中建立hash表，然后掃描大表，每讀到一條記錄就探測(cè)hash表一次，找出與hash表匹配的行。

當(dāng)小表可以全部放入內(nèi)存中，其成本接近全表掃描兩個(gè)表的成本之和。如果表很大不能完全放入內(nèi)存，這時(shí)優(yōu)化器會(huì)將它分割成若干不同的分區(qū)，不能放入內(nèi)存的部分就把該分區(qū)寫(xiě)入磁盤(pán)的臨時(shí)段，此時(shí)要有較大的臨時(shí)段從而盡量提高I/O 的性能。臨時(shí)段中的分區(qū)都需要換進(jìn)內(nèi)存做hash join。這時(shí)候成本接近于全表掃描小表+分區(qū)數(shù)*全表掃描大表的代價(jià)和。

至于兩個(gè)表都進(jìn)行分區(qū)，其好處是可以使用parallel query，就是多個(gè)進(jìn)程同時(shí)對(duì)不同的分區(qū)進(jìn)行join，然后再合并。但是復(fù)雜。

使用hash join時(shí)，HASH_AREA_SIZE初始化參數(shù)必須足夠的大，如果是9i，Oracle建議使用SQL工作區(qū)自動(dòng)管理，設(shè)置WORKAREA_SIZE_POLICY 為AUTO，然后調(diào)整PGA_AGGREGATE_TARGET即可。

以下條件下hash join可能有優(yōu)勢(shì)：
1）兩個(gè)巨大的表之間的連接。
2）在一個(gè)巨大的表和一個(gè)小表之間的連接。

要點(diǎn)如下：
1）散列連接是CBO 做大數(shù)據(jù)集連接時(shí)的常用方式.
2）也可以用USE_HASH(table_name1 table_name2)提示來(lái)強(qiáng)制使用散列連接
3）Hash join在兩個(gè)表的數(shù)據(jù)量差別很大的時(shí)候.
4）Hash join的工作方式是將一個(gè)表（通常是小一點(diǎn)的那個(gè)表）做hash運(yùn)算并存儲(chǔ)到hash列表中，從另一個(gè)表中抽取記錄，做hash運(yùn)算，到hash 列表中找到相應(yīng)的值，做匹配。

可用ordered提示來(lái)改變CBO默認(rèn)的驅(qū)動(dòng)表，可用USE_HASH(table_name1 table_name2)提示來(lái)強(qiáng)制使用hash join。

3. SORT MERGE JOIN

a）對(duì)連接的每個(gè)表做table access full；
b）對(duì)table access full的結(jié)果進(jìn)行排序；
c）進(jìn)行merge join對(duì)排序結(jié)果進(jìn)行合并。

sort merge join性能開(kāi)銷(xiāo)幾乎都在前兩步。一般是在沒(méi)有索引的情況下，9i開(kāi)始已經(jīng)很少出現(xiàn)，因?yàn)槠渑判虺杀靖撸蠖酁閔ash join替代。
通常情況下hash join的效果都比sort merge join要好，但是，如果行源已經(jīng)被排過(guò)序，在執(zhí)行sort merge join時(shí)不需要再排序，這時(shí)sort merge join的性能會(huì)優(yōu)于hash join。
當(dāng)全表掃描比“索引范圍掃描后再通過(guò)rowid進(jìn)行表訪問(wèn)”更可取的情況下，sort merge join會(huì)比nested loops性能更佳。

要點(diǎn)如下：

1）使用USE_MERGE(table_name1 table_name2)來(lái)強(qiáng)制使用排序合并連接.
2）Sort Merge join 用在沒(méi)有索引，并且數(shù)據(jù)已經(jīng)排序的情況.
3）連接步驟：將兩個(gè)表排序，然后將兩個(gè)表合并。
4）通常情況下，只有在以下情況發(fā)生時(shí)，才會(huì)使用此種JOIN方式：
a）RBO模式
b）不等價(jià)關(guān)聯(lián)(>,,>=,=,>)
c）bHASH_JOIN_ENABLED=false
d）數(shù)據(jù)源已排序
e）Merge Join 是先將關(guān)聯(lián)表的關(guān)聯(lián)列各自做排序，然后從各自的排序表中抽取數(shù)據(jù)，到另一個(gè)排序表中做匹配，因?yàn)閙erge join需要做更多的排序，所以消耗的資源更多。

f) like ,not like
通常來(lái)講，能夠使用merge join的地方，hash join都可以發(fā)揮更好的性能

可用USE_MERGE(table_name1 table_name2)提示強(qiáng)制使用sort merge join。