淺談Oracle中的三種Join方法

基本概念

Nested loop join：

Outer table中的每一行與inner table中的相應(yīng)記錄join，類似一個(gè)嵌套的循環(huán)。

Sort merge join：

將兩個(gè)表排序，然后再進(jìn)行join。

Hash join：

將兩個(gè)表中較小的一個(gè)在內(nèi)存中構(gòu)造一個(gè)Hash 表（對(duì)Join Key），掃描另一個(gè)表，同樣對(duì)Join Key進(jìn)行Hash后探測(cè)是否可以join，找出與之匹配的行。

一張小表被hash在內(nèi)存中。因?yàn)閿?shù)據(jù)量小，所以這張小表的大多數(shù)數(shù)據(jù)已經(jīng)駐入在內(nèi)存中，剩下的少量數(shù)據(jù)被放置在臨時(shí)表空間中；

每讀取大表的一條記錄，就和小表中內(nèi)存中的數(shù)據(jù)進(jìn)行比較，如果符合，則立即輸出數(shù)據(jù)（也就是說沒有讀取臨時(shí)表空間中的小表的數(shù)據(jù)）。而如果大表的數(shù)據(jù)與小表中臨時(shí)表空間的數(shù)據(jù)相符合，則不直接輸出，而是也被存儲(chǔ)臨時(shí)表空間中。

當(dāng)大表的所有數(shù)據(jù)都讀取完畢，將臨時(shí)表空間中的數(shù)據(jù)以其輸出。如果小表的數(shù)據(jù)量足夠小（小于hash area size），那所有數(shù)據(jù)就都在內(nèi)存中了，可以避免對(duì)臨時(shí)表空間的讀寫。

如果是并行環(huán)境下，前面中的第2步就變成如下了：每讀取一條大表的記錄，和內(nèi)存中小表的數(shù)據(jù)比較，如果符合先做join，而不直接輸出，直到整張大表數(shù)據(jù)讀取完畢。如果內(nèi)存足夠，Join好的數(shù)據(jù)就保存在內(nèi)存中。否則，就保存在臨時(shí)表空間中。

適用范圍

Nested loop join:

適用于outer table(有的地方叫Master table)的記錄集比較少（10000）而且inner table（有的地方叫Detail table）索引選擇性較好的情況下（inner table要有index）。

inner table被outer table驅(qū)動(dòng)，outer table返回的每一行都要在inner table中檢索到與之匹配的行。當(dāng)然也可以用ORDERED 提示來改變CBO默認(rèn)的驅(qū)動(dòng)表，使用USE_NL(table_name1 table_name2)可是強(qiáng)制CBO 執(zhí)行嵌套循環(huán)連接。

cost = outer access cost + (inner access cost * outer cardinality)

Sort merge join：

用在數(shù)據(jù)沒有索引但是已經(jīng)排序的情況下。

通常情況下hash join的效果都比Sort merge join要好，然而如果行源已經(jīng)被排過序，在執(zhí)行排序合并連接時(shí)不需要再排序了，這時(shí)Sort merge join的性能會(huì)優(yōu)于hash join?？梢允褂肬SE_MERGE(table_name1 table_name2)來強(qiáng)制使用Sort merge join。
cost = (outer access cost * # of hash partitions) + inner access cost

Hash join：

適用于兩個(gè)表的數(shù)據(jù)量差別很大。但需要注意的是：如果HASH表太大，無法一次構(gòu)造在內(nèi)存中，則分成若干個(gè)partition，寫入磁盤的temporary segment，則會(huì)多一個(gè)I/O的代價(jià)，會(huì)降低效率，此時(shí)需要有較大的temporary segment從而盡量提高I/O的性能。

可以用USE_HASH(table_name1 table_name2)提示來強(qiáng)制使用散列連接。如果使用散列連HASH_AREA_SIZE 初始化參數(shù)必須足夠的大，如果是9i，Oracle建議使用SQL工作區(qū)自動(dòng)管理，設(shè)置WORKAREA_SIZE_POLICY 為AUTO，然后調(diào)整PGA_AGGREGATE_TARGET 即可。

也可以使用HASH_JOIN_ENABLED=FALSE（默認(rèn)為TRUE）強(qiáng)制不使用hash join。

cost = (outer access cost * # of hash partitions) + inner access cost

效率比較

Hash join的主要資源消耗在于CPU（在內(nèi)存中創(chuàng)建臨時(shí)的hash表，并進(jìn)行hash計(jì)算），而merge join的資源消耗主要在于磁盤I/O（掃描表或索引）。在并行系統(tǒng)中，hash join對(duì)CPU的消耗更加明顯。所以在CPU緊張時(shí)，最好限制使用hash join。

在絕大多數(shù)情況下，hash join效率比其他join方式效率更高：

在Sort-Merge Join(SMJ)，兩張表的數(shù)據(jù)都需要先做排序，然后做merge。因此效率相對(duì)最差；

Nested-Loop Join(NL)效率比SMJ更高。特別是當(dāng)驅(qū)動(dòng)表的數(shù)據(jù)量很大（集的勢(shì)高）時(shí)。這樣可以并行掃描內(nèi)表。

Hash join效率最高，因?yàn)橹灰獙?duì)兩張表掃描一次。