1. NESTED LOOP
對于被連接的數(shù)據(jù)子集較小的情況,nested loop連接是個較好的選擇。nested loop就是掃描一個表,每讀到一條記錄,就根據(jù)索引去另一個表里面查找,沒有索引一般就不會是 nested loops。一般在nested loop中, 驅(qū)動表滿足條件結(jié)果集不大,被驅(qū)動表的連接字段要有索引,這樣就走nstedloop。如果驅(qū)動表返回記錄太多,就不適合nested loops了。如果連接字段沒有索引,則適合走h(yuǎn)ash join,因?yàn)椴恍枰饕?/P>
可用ordered提示來改變CBO默認(rèn)的驅(qū)動表,可用USE_NL(table_name1 table_name2)提示來強(qiáng)制使用nested loop。
要點(diǎn)如下:
1)對于被連接的數(shù)據(jù)子集較小的情況,嵌套循環(huán)連接是個較好的選擇
2)使用USE_NL(table_name1 table_name2)可是強(qiáng)制CBO 執(zhí)行嵌套循環(huán)連接
3)Nested loop一般用在連接的表中有索引,并且索引選擇性較好的時候
4)OIN的順序很重要,驅(qū)動表的記錄集一定要小,返回結(jié)果集的響應(yīng)時間是最快的。
5)Nested loops 工作方式是從一張表中讀取數(shù)據(jù),訪問另一張表(通常是索引)來做匹配,nested loops適用的場合是當(dāng)一個關(guān)聯(lián)表比較小的時候,效率會更高。
2. HASH JOIN
hash join是CBO 做大數(shù)據(jù)集連接時的常用方式。優(yōu)化器掃描小表(數(shù)據(jù)源),利用連接鍵(也就是根據(jù)連接字段計算hash 值)在內(nèi)存中建立hash表,然后掃描大表,每讀到一條記錄就探測hash表一次,找出與hash表匹配的行。
當(dāng)小表可以全部放入內(nèi)存中,其成本接近全表掃描兩個表的成本之和。如果表很大不能完全放入內(nèi)存,這時優(yōu)化器會將它分割成若干不同的分區(qū),不能放入內(nèi)存的部分就把該分區(qū)寫入磁盤的臨時段,此時要有較大的臨時段從而盡量提高I/O 的性能。臨時段中的分區(qū)都需要換進(jìn)內(nèi)存做hash join。這時候成本接近于全表掃描小表+分區(qū)數(shù)*全表掃描大表的代價和。
至于兩個表都進(jìn)行分區(qū),其好處是可以使用parallel query,就是多個進(jìn)程同時對不同的分區(qū)進(jìn)行join,然后再合并。但是復(fù)雜。
使用hash join時,HASH_AREA_SIZE初始化參數(shù)必須足夠的大,如果是9i,Oracle建議使用SQL工作區(qū)自動管理,設(shè)置WORKAREA_SIZE_POLICY 為AUTO,然后調(diào)整PGA_AGGREGATE_TARGET即可。
以下條件下hash join可能有優(yōu)勢:
1)兩個巨大的表之間的連接。
2)在一個巨大的表和一個小表之間的連接。
要點(diǎn)如下:
1)散列連接是CBO 做大數(shù)據(jù)集連接時的常用方式.
2)也可以用USE_HASH(table_name1 table_name2)提示來強(qiáng)制使用散列連接
3)Hash join在兩個表的數(shù)據(jù)量差別很大的時候.
4)Hash join的工作方式是將一個表(通常是小一點(diǎn)的那個表)做hash運(yùn)算并存儲到hash列表中,從另一個表中抽取記錄,做hash運(yùn)算,到hash 列表中找到相應(yīng)的值,做匹配。
可用ordered提示來改變CBO默認(rèn)的驅(qū)動表,可用USE_HASH(table_name1 table_name2)提示來強(qiáng)制使用hash join。
3. SORT MERGE JOIN
a)對連接的每個表做table access full;
b)對table access full的結(jié)果進(jìn)行排序;
c)進(jìn)行merge join對排序結(jié)果進(jìn)行合并。
sort merge join性能開銷幾乎都在前兩步。一般是在沒有索引的情況下,9i開始已經(jīng)很少出現(xiàn),因?yàn)槠渑判虺杀靖?,大多為hash join替代。
通常情況下hash join的效果都比sort merge join要好,但是,如果行源已經(jīng)被排過序,在執(zhí)行sort merge join時不需要再排序,這時sort merge join的性能會優(yōu)于hash join。
當(dāng)全表掃描比“索引范圍掃描后再通過rowid進(jìn)行表訪問”更可取的情況下,sort merge join會比nested loops性能更佳。
要點(diǎn)如下:
1)使用USE_MERGE(table_name1 table_name2)來強(qiáng)制使用排序合并連接.
2)Sort Merge join 用在沒有索引,并且數(shù)據(jù)已經(jīng)排序的情況.
3)連接步驟:將兩個表排序,然后將兩個表合并。
4)通常情況下,只有在以下情況發(fā)生時,才會使用此種JOIN方式:
a)RBO模式
b)不等價關(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ù),到另一個排序表中做匹配,因?yàn)閙erge join需要做更多的排序,所以消耗的資源更多。
f) like ,not like
通常來講,能夠使用merge join的地方,hash join都可以發(fā)揮更好的性能
可用USE_MERGE(table_name1 table_name2)提示強(qiáng)制使用sort merge join。
更多信息請查看IT技術(shù)專欄