Oracle優(yōu)化器的基礎(chǔ)知識(shí)(二)

一、 表連接

      顧名思義，表連接就是指多個(gè)表之間用連接條件連接在一起，使用表連接的目標(biāo)SQL的目的就是從多個(gè)表獲取存儲(chǔ)在這些表中的不同維度的數(shù)據(jù)。體現(xiàn)在SQL語(yǔ)句上，含表連接的目標(biāo)SQL的from部分會(huì)出現(xiàn)多個(gè)表，而這些SQL的where條件部分則會(huì)定義具體的表連接條件。

      當(dāng)優(yōu)化器解析含表連接的目標(biāo)SQL時(shí)，它除了會(huì)根據(jù)目標(biāo)SQL的SQL文本的寫法來(lái)決定表連接的類型之外，還必須決定如下三件事情才能得到最終的執(zhí)行計(jì)劃。

1.表連接順序

      不管目標(biāo)SQL中有多少個(gè)表做表連接，Oracle在實(shí)際執(zhí)行該SQL時(shí)都只能先兩兩做表連接，再依次執(zhí)行這樣的兩兩表連接過(guò)程，直到目標(biāo)SQL中所有的表都已連接完畢。從嚴(yán)格意義上來(lái)說(shuō)，這里的表連接順序包含兩層含義：一層含義是當(dāng)兩個(gè)表做表連接時(shí)，優(yōu)化器需要決定這兩個(gè)表中誰(shuí)是驅(qū)動(dòng)表(outer table)，誰(shuí)是被驅(qū)動(dòng)表(inner table)；另外一層含義是當(dāng)多表(超過(guò)兩個(gè)以上的表)做表連接時(shí)，優(yōu)化器需要決定這些表中誰(shuí)和誰(shuí)先做表連接，然后決定這個(gè)表連接結(jié)果所在的結(jié)果集再和剩余表中的哪一個(gè)再做表連接，這個(gè)兩兩表連接的過(guò)程會(huì)一直持續(xù)下去，直到目標(biāo)SQL中所有的表都已經(jīng)連接完為止。

2.表連接方法

      在Oracle數(shù)據(jù)庫(kù)中，兩個(gè)表之間的表連接方法有排序合并(sort merge join)、嵌套循環(huán)連接(nested loops join)、哈希連接(hash join)和笛卡兒連接(cross join)這4種，所以優(yōu)化器在解析含表連接的目標(biāo)SQL時(shí)，都需要從上述四種方法中選擇一種，作為每一對(duì)表兩兩做表連接時(shí)所需要采用的方法。

3.訪問(wèn)單表的方法

      對(duì)于優(yōu)化器而言，僅決定表連接順序和表連接方法是不夠的，這還不中以得到目標(biāo)SQL的最終執(zhí)行計(jì)劃，因?yàn)閮?yōu)化器在對(duì)目標(biāo)SQL中的各個(gè)表兩兩做表連接時(shí)，還必須決定如何去獲取存儲(chǔ)在這些表里的不同維度的數(shù)據(jù)，即優(yōu)化器還要決定訪問(wèn)單表的方法。比如在訪問(wèn)某個(gè)單表時(shí)，是采用全表掃描還是走索引，如果是走索引，應(yīng)該采用什么樣的索引訪問(wèn)方法等。

1.1 表連接的類型

通常情況下，我們可以認(rèn)為Oracle數(shù)據(jù)庫(kù)中的表連接分為內(nèi)連接和外連接這兩種類型，表連接的類型會(huì)直接決定表連接的結(jié)果，而目標(biāo)SQL的SQL文本的寫法又直接決定了表連接的類型。

1.1.1 內(nèi)連接

內(nèi)連接(Inner Join)是指表連接的結(jié)果只包含那些完全滿足連接條件的記錄。對(duì)于包含表連接的目標(biāo)SQL而言，只要其where條件中沒(méi)有寫那些標(biāo)準(zhǔn)SQL中定義或者Oracle中自定義的表示外連接的關(guān)鍵字(比如標(biāo)準(zhǔn)SQL中的left outer join、right outer join、full outer join，或者Oracle中自定義的用來(lái)表示外連接的關(guān)鍵字“(+)”)，則該SQL的連接類型就是內(nèi)連接。

Oracle自定義的內(nèi)連接寫法：

      目標(biāo)表1,目標(biāo)表2 where 連接條件

標(biāo)準(zhǔn)SQL中內(nèi)連接是用JOIN ON或者JOIN USING。

   JOIN ON的語(yǔ)法：

      目標(biāo)表1 join 目標(biāo)表2 on (連接條件)

   JOIN USING的語(yǔ)法：

      目標(biāo)表1 join 目標(biāo)表2 using(連接列集合)。

對(duì)于使用JOIN USING的目標(biāo)SQL而言，如果有多個(gè)連接列，其語(yǔ)法中“(連接列集合)”里的各個(gè)連接列之間應(yīng)使用逗號(hào)來(lái)分隔。需要注意的時(shí)，使用JOIN USING的連接語(yǔ)法，如果連接列同時(shí)又出現(xiàn)在查詢列中，則該連接列前不能帶上表名或者表名的別名(alias),否則Oracle會(huì)報(bào)錯(cuò)(ORA-25154)。

標(biāo)準(zhǔn)SQL中還有一種特殊的JOIN USING，我們稱之為NATURAL JOIN，其含義是使用NATURAL JOIN的表連接的連接列是表連接的兩個(gè)表所有的同名列。語(yǔ)法：

      目標(biāo)表1 natural join 目標(biāo)表2

這實(shí)際相當(dāng)于目標(biāo)表1 join 目標(biāo)表2 using(目標(biāo)表1和目標(biāo)表2的所有同名列集合)。使用NATURAL JOIN好外是無(wú)須寫連接列集合，但其壞處是增加了表連接的執(zhí)行結(jié)果出錯(cuò)的風(fēng)險(xiǎn)，因?yàn)閮蓚€(gè)表之間的同名列不一定在含義上就完全相同，也許只是恰好同名，而即使含義相同，也不一定就需要將它們作為連接列。

1.1.2 外連接

      外連接(Outer Join)是對(duì)內(nèi)連接的一種擴(kuò)展，它是指表連接的連接結(jié)果除了包含那些完全滿足連接條件的記錄之外還會(huì)包含驅(qū)動(dòng)表中所有不滿足該條件的記錄。

      標(biāo)準(zhǔn)SQL中的外連接分為左外連接(Left Outer Join)、右連接(Right Outer Join)和全連接(Full Outer Join)這三種，它們?cè)跇?biāo)準(zhǔn)SQL中對(duì)應(yīng)的關(guān)鍵字分別為left outer join、right outer join、full outer join，都可以和JOIN ON 或JOIN USING連用。

左連接的語(yǔ)法：

      目標(biāo)表1 left outer join 目標(biāo)表2 on(連接條件) 或目標(biāo)表1 left outer join 目標(biāo)表2 using (連接列集合)

      其含義是目標(biāo)表1和目標(biāo)表2按括號(hào)中的連接條件來(lái)做表連接，位于關(guān)鍵字左邊的表1作為驅(qū)動(dòng)表(outer table)，此時(shí)的連接結(jié)果包含了表1和表2中所有滿足該連接條件的記錄外，還會(huì)包含驅(qū)動(dòng)表(表1)中所有不滿足該連接條件的記錄，同時(shí)，驅(qū)動(dòng)表中所有不滿足該連接條件的記錄所對(duì)應(yīng)的被驅(qū)動(dòng)表(表2)中的查詢列均會(huì)以NULL值來(lái)填充。

右連接的語(yǔ)法：

      目標(biāo)表1 right outer join 目標(biāo)表2 on(連接條件) 或目標(biāo)表1 right outer join 目標(biāo)表2 using (連接列集合)

      含義與左連接相似，不過(guò)，這次位于關(guān)鍵字右表的表2為驅(qū)動(dòng)表。

全連接語(yǔ)法：

      目標(biāo)表1 full outer join 目標(biāo)表2 on(連接條件) 或目標(biāo)表1 full outer join 目標(biāo)表2 using (連接列集合)

      其含義是目標(biāo)表1和目標(biāo)表2按括號(hào)中的連接條件來(lái)做表連接。此時(shí)的連接結(jié)果除了包含表1和表2中所有滿足該連接條件的記錄外，還會(huì)包含目標(biāo)表1和目標(biāo)表2中所有不滿足該連接條件的記錄，同時(shí)，表1和表2中所有不滿足該連接條件的記錄所對(duì)就的另外一個(gè)表中的查詢列均會(huì)以NULL值來(lái)填充。

      上面介紹的范例SQL中除了帶連接條件外，并沒(méi)有帶其他額外的限制條件。如果目標(biāo)SQL中除了表連接條件之外還帶了額外的限制條件，則目標(biāo)SQL中表連接的類型和該額外限制條件在目標(biāo)SQL的SQL文本中出現(xiàn)的位置都可能會(huì)對(duì)最終執(zhí)行結(jié)果產(chǎn)生影響。

      內(nèi)連接添加其他限制條件實(shí)例：

      對(duì)內(nèi)連接而言，除了表連接條件之外的額外限制條件在目標(biāo)SQL的SQL文本中所處的位置不會(huì)影響該SQL的實(shí)際執(zhí)行結(jié)果。

      外連接添加其他限制條件實(shí)例：

      對(duì)于外連接而言，如果額外限制條件在外連接關(guān)鍵字對(duì)應(yīng)的括號(hào)內(nèi)，這表示該限制條件會(huì)在表t1和表t2做右連接之前就被應(yīng)用在表t1上，而如果額外限制條件在外連接關(guān)鍵字對(duì)應(yīng)的括號(hào)外，表示該限制條件在表t1和表t2做完右連接后，才會(huì)被應(yīng)用在表t1和表t2的連接結(jié)果集上。

      所以，對(duì)于外連接而言，除了表連接條件之外的額外限制條件在目標(biāo)SQL的SQL文本中所處的位置確實(shí)可能會(huì)影響該SQL的實(shí)際執(zhí)行結(jié)果。

      和標(biāo)準(zhǔn)SQL里表示外連接的語(yǔ)法不同，Oracle用自定義的關(guān)鍵字“(+)”來(lái)表示外連接。關(guān)鍵字“(+)”的位置在目標(biāo)SQL連接條件中某一個(gè)表的連接列后面，其含義是關(guān)鍵字“(+)”出現(xiàn)在哪個(gè)表的連接列后面，就表明哪個(gè)表會(huì)以NULL值來(lái)填充那不滿足連接條件找位置該表中的查詢列，此時(shí)應(yīng)該以關(guān)鍵字“(+)”對(duì)應(yīng)的表作為外連接的驅(qū)動(dòng)表，這是的關(guān)鍵是哪個(gè)表是驅(qū)動(dòng)表！

      之前提到過(guò)：對(duì)于外連接而言，表連接條件之外的額外限制條件在目標(biāo)SQL的SQL文本中所處位置的不同可能會(huì)影響該SQL的實(shí)際執(zhí)行結(jié)果。那如果使用Oracle自定義的關(guān)鍵字“(+)”來(lái)表示外連接的話，那么如何體現(xiàn)呢？很簡(jiǎn)單，Oracle是通過(guò)在額外限制條件的目標(biāo)列的后面帶上同樣的關(guān)鍵字“(+)”來(lái)體現(xiàn)出上述影響的：

select t1.col1,t1.col2,t2.col3

from t1,t2

where t1.col2(+)=t2.col2

and t1.col1(+)=1;

      前面提到的NATURAL JOIN不僅適用于內(nèi)連接，也同樣適用于外連接：

select t1.col1,col2,t2.col3

from t1 natural left outer join t2 ;

1.2 表連接的方法

      之前介紹過(guò)，優(yōu)化器在解析含表連接的目標(biāo)SQL時(shí)，當(dāng)它根據(jù)目標(biāo)SQL的SQL文本的寫法決定表連接的類型之后，接下來(lái)要做的事情之一就是決定表連接的方法。

      在Oracle數(shù)據(jù)庫(kù)中，兩個(gè)表之間的表連接方法有排序合并連接、嵌套循環(huán)連接、哈希連接和笛卡兒連接這四種。這四種表連接各有優(yōu)缺點(diǎn)，也各有其適用場(chǎng)景，接下來(lái)分別介紹它們

1.2.1 排序合并連接

      排序合并連接(Sort Merge Join)是一種兩個(gè)表在做表連接時(shí)用排序操作(Sort)和合并操作(Merge)來(lái)得到連接結(jié)果集的表連接方法。

      如果兩個(gè)表(假如為T1和T2)做表連接時(shí)使用的是排序合并連接，則Oracle會(huì)依次順序執(zhí)行如下步驟：

1. 首先以目標(biāo)SQL中指定的謂詞條件(如果有的話)去訪問(wèn)表T1，然后對(duì)訪問(wèn)結(jié)果按照表T1中的連接來(lái)排序，排好序后的結(jié)果集我們記為結(jié)果集1。

2. 接著以目標(biāo)SQL中指定的謂詞條件(如果有的話)去訪問(wèn)表T2，然后對(duì)訪問(wèn)結(jié)果按照表T2中的連接來(lái)排序，排好序后的結(jié)果集我們記為結(jié)果集2。

3. 最后對(duì)結(jié)果集1和結(jié)果集2執(zhí)行合并操作，從中取出匹配記錄來(lái)作為排序合并連接的最終執(zhí)行結(jié)果。

      對(duì)于排序合并連接的優(yōu)缺點(diǎn)及適用場(chǎng)景，總結(jié)如下：

• 通常情況下，排序合并連接的執(zhí)行效率會(huì)遠(yuǎn)不如哈希連接，但前者的使用范圍更廣，因?yàn)楣＿B接通常只能用于等值連接，而排序合并連接并不能用于其他條件(例如<、<=、>、>=)。

• 通常情況下，排序合并連接并不短途OLTP類型的系統(tǒng)，其本質(zhì)原因是因?yàn)閷?duì)OLTP類型的系統(tǒng)而言，排序是非常昂貴的操作，當(dāng)然，如果能避免排序操作，那么即使是OLTP類型的系統(tǒng)，也還是可以使用排序合并連接的。比如兩個(gè)表雖然是排序合并連接，但實(shí)際上它們并不需要排序，因?yàn)檫@兩個(gè)表各自的連接列上都存在索引。

• 從嚴(yán)格意義上說(shuō)，排序合并連接不存在驅(qū)動(dòng)表的概念。

1.2.2 嵌套循環(huán)連接

      嵌套循環(huán)連接(Nested Loops Join)是一種兩個(gè)表在做表連接時(shí)依靠?jī)蓪忧短籽h(huán)(分別為外層循環(huán)和內(nèi)層循環(huán))來(lái)得到連接結(jié)果集的表連接方法。

      如果兩個(gè)表(假如為T1和T2)在做表連接時(shí)使用的是嵌套循環(huán)連接，則Oracle會(huì)依次順序執(zhí)行如下步驟：

1. 首先，優(yōu)化器會(huì)按照一定的規(guī)則來(lái)決定表T1和T2中誰(shuí)是驅(qū)動(dòng)表、誰(shuí)是被驅(qū)動(dòng)表。驅(qū)動(dòng)表用于外層循環(huán)，被驅(qū)動(dòng)表用于內(nèi)存循環(huán)。這是假設(shè)驅(qū)動(dòng)表是T1，被驅(qū)動(dòng)表是T2。

2. 接著以目標(biāo)SQL中指定的謂詞條件(如果有的話)去訪問(wèn)驅(qū)動(dòng)表T1，訪問(wèn)驅(qū)動(dòng)表T1后得到的結(jié)果集我們記為驅(qū)動(dòng)結(jié)果集1。

3. 然后遍歷驅(qū)動(dòng)結(jié)果集1并同時(shí)遍歷被驅(qū)動(dòng)表T2，即先取出驅(qū)動(dòng)結(jié)果集1中的第1條記錄，接著遍歷被驅(qū)動(dòng)表T2并按照連接條件去判斷T2中是否存在匹配的記錄，然后再取出驅(qū)動(dòng)結(jié)果集1中的第2條記錄，按照同樣的連接條件再去遍歷被驅(qū)動(dòng)表T2并判斷T2中是否還存在匹配的記錄，直到遍歷完驅(qū)動(dòng)結(jié)果集1中所有的記錄為止。這里的外層循環(huán)是指遍歷驅(qū)動(dòng)結(jié)果集1所對(duì)應(yīng)的循環(huán)，內(nèi)層循環(huán)是指遍歷被驅(qū)動(dòng)表T2所對(duì)應(yīng)的循環(huán)。顯然，外層循環(huán)所對(duì)應(yīng)的驅(qū)動(dòng)結(jié)果集1有多少條記錄，遍歷被驅(qū)動(dòng)表T2的內(nèi)層循環(huán)就要做多少次，這就是所謂的“嵌套循環(huán)”的含義。

      嵌套循環(huán)連接的優(yōu)缺點(diǎn)及適用場(chǎng)景總結(jié)如下：

• 從上述嵌套循環(huán)連接的具體執(zhí)行過(guò)程可以看出：如果驅(qū)動(dòng)表所對(duì)應(yīng)的驅(qū)動(dòng)結(jié)果集的記錄數(shù)較少，同時(shí)被驅(qū)動(dòng)表的連接列上又存在唯一性索引(或者在被驅(qū)動(dòng)表的連接列上存在選擇性很的的非唯一性索引)，那么此時(shí)使用嵌套循環(huán)連接的執(zhí)行效率就會(huì)非常高；但如果驅(qū)動(dòng)表所對(duì)應(yīng)的驅(qū)動(dòng)結(jié)果集的記錄數(shù)很多，即便在被驅(qū)動(dòng)表的連接列上存在索引，此時(shí)使用嵌套循環(huán)連接的執(zhí)行效率也不會(huì)高。

• 只要驅(qū)動(dòng)結(jié)果集的記錄數(shù)較少，那就具備了做嵌套循環(huán)連接的前提條件，而驅(qū)動(dòng)結(jié)果集是在對(duì)驅(qū)動(dòng)表應(yīng)用了目標(biāo)SQL中指定的謂詞條件(如果有的話)后所得到的結(jié)果集，所以大表也可以作為嵌套循環(huán)連接的驅(qū)動(dòng)表，關(guān)鍵看目標(biāo)SQL中指定的謂詞條件(如果有的話)能否將驅(qū)動(dòng)結(jié)果集的數(shù)據(jù)量降下來(lái)。

• 嵌套循環(huán)連接有其他連接方法所沒(méi)有的一個(gè)優(yōu)點(diǎn)：嵌套循環(huán)連接可以實(shí)現(xiàn)快速響應(yīng)，即它可以第一時(shí)間返回已經(jīng)連接過(guò)具滿足連接條件的記錄，而不必等待所有的連接操作全部做完才返回連接結(jié)果。雖然排序合并連接也可以，但它們并不是第一時(shí)間返回，因?yàn)榕判蚝喜⑦B接要等到排序完后做合并操作時(shí)才能開(kāi)始返回?cái)?shù)據(jù)，而哈希連接則要等到驅(qū)動(dòng)結(jié)果集所對(duì)應(yīng)的Hash Table全部建完后才能開(kāi)始返回?cái)?shù)據(jù)。

      如果Oracle使用的是嵌套循環(huán)連接，且在被驅(qū)動(dòng)表的連接列上存在索引，那么Oracle在訪問(wèn)索引時(shí)通常會(huì)使用單塊讀，這意味著嵌套循環(huán)連接的驅(qū)動(dòng)結(jié)果集有多少條記錄，Oracle就會(huì)需要訪問(wèn)該索引多少次。另外，如果目標(biāo)SQL中的查詢列并不能全部從驅(qū)動(dòng)表的相關(guān)索引中獲得，那么Oracle在做完嵌套循環(huán)連接后還需要對(duì)被驅(qū)動(dòng)表執(zhí)行回表操作。這個(gè)回青操作通常也會(huì)使用單塊讀，這意味著做完嵌套循環(huán)連接后的連接結(jié)果集有多少條記錄，Oracle就需要回表多少次。

      為了提高嵌套循環(huán)連接的執(zhí)行效率，在Oracle 11g中，Oracle引入了向量I/O(Vector I/O)。在引入向量I/O后，Oracle就可以將原先一批單塊讀所需要耗費(fèi)的物理I/O結(jié)合起來(lái)，然后用一個(gè)向量I/O去批處理它們，這樣就實(shí)現(xiàn)了在單塊讀的數(shù)量不降低的情況下減少這些單塊讀所需要耗費(fèi)的物理I/O數(shù)量，也就提高了嵌套循環(huán)連接的執(zhí)行效率。

1.2.3 哈希連接

      哈希連接(Hash Join)是一種兩個(gè)表在做表連接時(shí)主要依靠哈希運(yùn)算來(lái)得到連接結(jié)果集的表連接方法。

      在Oracle7.3之前，Oracle數(shù)據(jù)庫(kù)中常用的表連接方法就只有排序合并連接和嵌套循環(huán)連接這兩種，但這兩種方法都各有其明顯缺陷。對(duì)于排序合并連接，如果兩個(gè)表在施加了目標(biāo)SQL中指定的謂詞條件(如果有的話)后得到的結(jié)果集很大且需要排序，則排序合并連接的執(zhí)行效率一定不高；而對(duì)于嵌套循環(huán)連接，如果驅(qū)動(dòng)表所對(duì)應(yīng)的驅(qū)動(dòng)結(jié)果集的記錄數(shù)很大，即便在被驅(qū)動(dòng)表的連接列上存在索引，此時(shí)使用嵌套循環(huán)連接的執(zhí)行效率也會(huì)同樣不高。為了上述情形下效率不高的問(wèn)題，同時(shí)也為了給優(yōu)化器提供一種新的選擇，Oracle在7.3中引入了哈希連接。從理論上來(lái)說(shuō)，哈希連接的執(zhí)行效率會(huì)比排序合并連接和嵌套循環(huán)連接要高，當(dāng)然，實(shí)際情況并不總是這樣。

      在Oracle 10g及其以后的Oracle數(shù)據(jù)庫(kù)版本中，優(yōu)化器(實(shí)際上是CBO，因?yàn)楣＿B接僅適用于CBO)在解析目標(biāo)SQL時(shí)是否考慮哈希連接是受限于隱含參數(shù)_HASH_JOIN_ENABLED，而在Oracle10g以前，CBO在解析目標(biāo)SQL時(shí)是否考慮哈希連接是受限于參數(shù)HASH_JOIN_ENABLED。_HASH_JOIN_ENABLED的默認(rèn)值是TRUE，表示允許CBO在解析目標(biāo)SQL時(shí)考慮哈希連接。當(dāng)然，即使該參數(shù)為FALSE，使用USE_HASH Hint依然可以讓CBO在解析目標(biāo)SQL時(shí)考慮哈希連接，這說(shuō)明USE_HASH Hint的優(yōu)先級(jí)比參數(shù)_HASH_JOIN_ENABLED的優(yōu)先級(jí)要高。

  如果兩個(gè)表(假如為T1和T2)在做表連接時(shí)使用的是哈希連接，則Oracle會(huì)依次順序執(zhí)行如下步驟：

1. 首先Oracle會(huì)根據(jù)參數(shù)HASH_AREAS_SIZE、DB_BLOCK_SIZE和_HASH_MULTIBLOCK_IO_COUNT的值來(lái)決定Hash Partition的數(shù)據(jù)(Hash Partition是一個(gè)邏輯上的概念，它實(shí)際上是一組Hash Bucket的集合。所有Hash Partition的集合就被稱為Hash Table，即一個(gè)Hash Table由多個(gè)Hash Partition所組成，而一個(gè)HashPartition又由多個(gè)Hash Bucket所組成的)。

2. 表T1和T2在施加了目標(biāo)SQL中指定的謂詞條件(如果有的話)后，得到的結(jié)果集中數(shù)量較少的那個(gè)結(jié)果集會(huì)被Oracle選為哈希連接的驅(qū)動(dòng)結(jié)果集，這里我們假設(shè)T1所對(duì)應(yīng)的結(jié)果集的數(shù)據(jù)量相對(duì)較少，記為S；T2所對(duì)應(yīng)的結(jié)果集的數(shù)據(jù)相對(duì)較多，記為B。顯然這里S是驅(qū)動(dòng)結(jié)果集，B是被驅(qū)動(dòng)結(jié)果集。

3. 接著Oracle會(huì)遍歷S，讀取S中的每一條記錄，并對(duì)每一條記錄按照該記錄在表T1中的連接列做哈希運(yùn)算。這個(gè)哈希運(yùn)算會(huì)使用兩個(gè)內(nèi)置哈希函數(shù)，這兩個(gè)哈希函數(shù)會(huì)同時(shí)對(duì)該連接列計(jì)算哈希值，我們把這兩個(gè)內(nèi)置哈希函數(shù)分別記為hash_func_1和hash_func_2，它們所計(jì)算出來(lái)的哈希值分別記為hash_vale_1和hash_value_2。

4. 然后Oracle會(huì)按照hash_value_1的值把相應(yīng)的S中的對(duì)應(yīng)記錄存儲(chǔ)在不同的Hash Partition的不同Hash Bucket里，同時(shí)和該記錄存儲(chǔ)在一起的還有該記錄用hash_func_2計(jì)算出來(lái)的hash_value_2。注意，存儲(chǔ)在Hash Bucket里的記錄并不是目標(biāo)表的完整行記錄，只需要存儲(chǔ)位置目標(biāo)SQL中與目標(biāo)表相關(guān)的查詢列和連接列就足夠了。我們把S所對(duì)應(yīng)的每一個(gè)Hash Partition記為Si。

5. 在構(gòu)建Si的同時(shí)，Oracle會(huì)構(gòu)建一個(gè)位圖(BITMAT)，這個(gè)位置用來(lái)標(biāo)記Si所包含的每一個(gè)Hash Bucket是否記錄(即記錄數(shù)是否大于0)。

6. 如果S的數(shù)據(jù)量很大，那么在構(gòu)建S所對(duì)應(yīng)的Hash Table時(shí)，就可能會(huì)出現(xiàn)PGA的工作區(qū)(WORK AREA)被填滿的情況。這時(shí)候Oracle會(huì)把工作區(qū)中包含記錄數(shù)最多的Hash Partition寫到磁盤上(TEMP表空間)。接著Oracle會(huì)繼續(xù)構(gòu)建S所對(duì)應(yīng)的Hash Table，在繼續(xù)構(gòu)建的過(guò)程中，如果工作區(qū)又滿了，則Oracle會(huì)繼續(xù)重復(fù)上述動(dòng)作，即挑選包含記錄數(shù)最多的Hash Partition并寫回到磁盤上。如果要構(gòu)建的記錄所對(duì)應(yīng)的Hash Partition已經(jīng)事先被Oracle寫回磁盤，則此時(shí)Oracle會(huì)去磁盤上更新Hash Partition ，即把該條記錄和Hash_vale_2直接加到這個(gè)已經(jīng)位于磁盤上的Hash Partition的相應(yīng)Hash Bucket中。注意，極端情況下可能會(huì)出現(xiàn)只有某個(gè)Hash Partition的部分記錄不覺(jué) 在內(nèi)存中，該Hash Partition的剩余部分和余下的所有Hash Partition都已經(jīng)被寫回到磁盤上。

7. 上述構(gòu)建S所對(duì)應(yīng)的Hash Table的過(guò)程會(huì)一直持續(xù)下去，直到遍歷完S中的所有記錄為止。

8. 接著，Oracle會(huì)對(duì)所有的Si按照它們所包含的記錄數(shù)來(lái)排序，然后把這些已經(jīng)誹好序的Hash Partition按順序依次且盡可能全部放到內(nèi)存中(PGA的工作區(qū))，當(dāng)然，如果實(shí)在放不下，放不下的那部分Hash Partition還是會(huì)位于磁盤上。

9. 至此Oracle已經(jīng)處理完S，現(xiàn)在可以開(kāi)始處理B了。

10. Oracle會(huì)遍歷B，讀取B中的每一條記錄，并按照該記錄在表T2中的連接列做哈希運(yùn)算，這個(gè)哈希運(yùn)算和步驟3中的哈希運(yùn)算是一模一樣的，即還是會(huì)用步驟3中的hash_func_1和hash_func_2，并且也會(huì)計(jì)算出兩個(gè)哈希值hash_value_1和hash_value_2。

    接著Oracle會(huì)按照該記錄所對(duì)應(yīng)的哈希值hash_value_1去Si里找匹配的Hash Bucket中的每一條記錄的連接列，看是否是真的匹配(即這里要校驗(yàn)S和B中匹配記錄所對(duì)應(yīng)的連接列是否真的相等，因?yàn)閷?duì)于哈希運(yùn)算而言，不同的值經(jīng)過(guò)哈希運(yùn)算后的結(jié)果可能是相同的)。如果真的匹配，則上述hash_value_1所對(duì)應(yīng)B中記錄的位于目標(biāo)SQL中的查詢列和該Hash Bucket中的匹配記錄便會(huì)組合起來(lái)，一起作為滿足目標(biāo)SQL連接條件的記錄返回。如果找不到匹配的Hash Bucket，則Oracle就會(huì)去訪問(wèn)步驟5中構(gòu)建的位圖。

    如果位圖顯示該Hash Bucket在Si中對(duì)應(yīng)的記錄數(shù)大于0，則說(shuō)明該Hash Bucket雖然不在內(nèi)存中，但它已經(jīng)被寫回磁盤，此時(shí)Oracle就會(huì)按照hash_value_1的值把相應(yīng)B中的對(duì)應(yīng)記錄也可以Hahs Partition的方式寫回到磁盤上，同時(shí)和該記錄存儲(chǔ)在一起的還有該記錄用hash_func_2計(jì)算出來(lái)的hash_value_2的值。如果位圖顯示該Hash Bucket在Si中對(duì)應(yīng) 的記錄數(shù)等于0，則Oralce就無(wú)須把上述hash_value_1所對(duì)應(yīng)B中的記錄寫回磁盤了，因?yàn)檫@條記錄必須不滿足目標(biāo)SQL的連接條件。這個(gè)根據(jù)位置來(lái)決定是否將hash_value_1所對(duì)就B的記錄寫回到磁盤的動(dòng)作就是所謂的“位圖過(guò)濾”(Oralce不一定會(huì)啟用位圖過(guò)濾，因?yàn)槿绻械?/span>Si本來(lái)就都在內(nèi)存中，也沒(méi)發(fā)生過(guò)將Si寫回到磁盤的操作，那么這里Oracle就不需要啟用位圖過(guò)濾了)。我們把B所對(duì)應(yīng)的每一個(gè)Hash Partition記為Bj。

11. 上述去Si中查找匹配Hash Bucket和構(gòu)建Bj的過(guò)程會(huì)一直持續(xù)下去，直到遍歷完B中的所有記錄為止。

12. 至此Oracle已經(jīng)處理完所有位于內(nèi)存中的Si和對(duì)應(yīng)的Bj，現(xiàn)在只剩下位于磁盤上的si和Bj還未處理。

13. 因?yàn)樵跇?gòu)建Si和Bj時(shí)用的是同樣的哈希函數(shù)hash_func_1和hash_func_2，所以Oracle在處理位于磁盤上的Si和Bj的時(shí)候可以放心地配對(duì)處理，即只有對(duì)應(yīng)Hash Partition     Number值相同的Si和Bj才可能會(huì)產(chǎn)生滿足連接條件的記錄。這里我們用Sn和Bn來(lái)表示位于磁盤上且對(duì)應(yīng)Hash Partition     Number值相同的Si和Bj。

14. 對(duì)于每一對(duì)Sn和Bn，它們之中記錄數(shù)較少的會(huì)被當(dāng)作驅(qū)動(dòng)結(jié)果集，然后Oracle會(huì)用這個(gè)驅(qū)動(dòng)結(jié)果集Hash Bucket里的記錄的hash_vale_2來(lái)構(gòu)建新的Hash Table，另外一個(gè)記錄數(shù)較多的會(huì)被當(dāng)作被驅(qū)動(dòng)結(jié)果集，然后Oracle會(huì)用這個(gè)被驅(qū)動(dòng)結(jié)果集Hash Bucket里記錄的hash_value_2去上述構(gòu)建的新的Hash Table中找匹配記錄。注意，對(duì)每一對(duì)Sn和Bn而言，Oracle始終會(huì)選擇它們中記錄數(shù)較少的來(lái)作為驅(qū)動(dòng)結(jié)果集，所以每一對(duì)Sn和Bn的驅(qū)動(dòng)結(jié)果集都可能會(huì)發(fā)生變化，這就是所謂的“動(dòng)態(tài)角色互換”。

15. 步驟14中如果存在匹配記錄，則該匹配記錄會(huì)作為滿足目標(biāo)SQL連接條件的記錄返回。

16. 上述處理Sn和Bn的過(guò)程會(huì)一直持續(xù)下去，直到遍歷完所有的Sn和Bn為止。

      哈希連接的優(yōu)缺點(diǎn)及適用場(chǎng)景總結(jié)如下：

• 哈希連接不一定會(huì)排序，或者說(shuō)大多數(shù)情況下都不需要排序。

• 哈希連接的驅(qū)動(dòng)表所對(duì)應(yīng)的連接列的可能性應(yīng)盡可能好，因?yàn)檫@個(gè)可選擇性會(huì)影響對(duì)應(yīng)Hash Bucket中的記錄數(shù)，而Hash Bucket中的記錄數(shù)又會(huì)直接影響從該Hash Bucket中查找匹配記錄的效率。如果一個(gè)Hash Bucket里所包含的記錄數(shù)過(guò)多，則可能會(huì)嚴(yán)重降低所對(duì)應(yīng)哈希連接的執(zhí)行效率，此時(shí)典型的表現(xiàn)就是該哈希連接執(zhí)行了很長(zhǎng)時(shí)間都沒(méi)有結(jié)束，數(shù)據(jù)庫(kù)所在數(shù)據(jù)庫(kù)服務(wù)器上的CPU占用率很高，但目標(biāo)SQL所消耗的邏輯讀卻很低，因?yàn)榇藭r(shí)大部分時(shí)間都耗費(fèi)在了遍歷上述Hash Bucket里的所有記錄上，而遍歷Hash Bucket里的記錄這個(gè)動(dòng)作發(fā)生在PGA的工作區(qū)里，所以不耗費(fèi)邏輯讀。

• 哈希連接只適用于CBO，它也只能用于等值連接條件(即使是哈希反連接，Oracle實(shí)際上也是將其轉(zhuǎn)換成了等價(jià)的等值連接)。

• 哈希連接很適合于小表和大表之間做表連接且連接結(jié)果集的記錄數(shù)較多的情形，特別是在小表的連接列的可選擇性非常好的情況下，哈希連接的執(zhí)行時(shí)間就可以近似看作是和全表掃描那個(gè)大表所耗費(fèi)的時(shí)間相當(dāng)。

• 當(dāng)兩個(gè)表做哈希連接時(shí)，如果在施加了目標(biāo)SQL中指定的謂詞條件(如果有的話)后得到的數(shù)據(jù)量較小的那個(gè)結(jié)果集所對(duì)應(yīng)的Hash Table能夠完全被容納在內(nèi)存(PGA的工作區(qū))，則此時(shí)的哈希連接的執(zhí)行效率會(huì)非常高。

1.2.4 笛卡兒連接

      笛卡兒連接(Cross Join)又稱為笛卡兒乘積(Caresian Product)，這是一種兩個(gè)表在做表連接時(shí)沒(méi)有任何連接條件的表連接方法。

      如果兩個(gè)表(假如為T1和T2)在做表連接時(shí)使用的是笛卡兒連接，則Oracle會(huì)依次順序執(zhí)行如下步驟：

1. 首先以目標(biāo)SQL中指定的謂詞條件(如果有的話)訪問(wèn)表T1，此時(shí)得到的結(jié)果集我們記為結(jié)果集1，這里假設(shè)結(jié)果集1的記錄數(shù)為m。

2. 接著以目標(biāo)SQL中指定的謂詞條件(如果有的話)訪問(wèn)表T2，此時(shí)得到的結(jié)果集我們記為結(jié)果集2，這里假設(shè)結(jié)果集2的記錄數(shù)為n。

3. 最后對(duì)結(jié)果集1和結(jié)果集2執(zhí)行合并操作，從中取出匹配記錄來(lái)作為笛卡兒連接的最終執(zhí)行結(jié)果。這里的特殊之處在于對(duì)于笛卡兒連接而言，因?yàn)樘视斜磉B接條件，所以在對(duì)結(jié)果集1和結(jié)果集2執(zhí)行合并操作時(shí)，對(duì)于結(jié)果集1中的任意一條記錄，結(jié)果集2中的所有記錄都滿足條件，即它們都會(huì)是匹配記錄，所以上述笛卡兒連接的連接結(jié)果的記錄數(shù)就是m和n的乘積(即m×n)。

     語(yǔ)句示例：select t1.col1,t2.col3 from t1,t2;

      標(biāo)準(zhǔn)SQL用關(guān)鍵字“CROSS JOIN”來(lái)表示笛卡兒連接，如select t1.col1,t2.col3 from t1 cross join t2;

      對(duì)于笛卡兒連接的優(yōu)缺點(diǎn)及適用場(chǎng)景總結(jié)如下：

• 笛卡兒連接的出現(xiàn)通常是由于目標(biāo)SQL中漏寫了表連接條件，所以笛卡兒連接一般是不好的，除非刻意這樣做(比如有些情況下可以利用笛卡兒連接來(lái)減少對(duì)目標(biāo)SQL中大表的全表掃描次數(shù))。

• 有時(shí)候出現(xiàn)笛卡兒連接是因?yàn)槟繕?biāo)SQL中使用了ORDERED Hint，同時(shí)在該SQL的SQL文本中位置相鄰的兩個(gè)表之間又沒(méi)有直接的關(guān)聯(lián)條件。

• 有時(shí)候出現(xiàn)笛卡兒連接是因?yàn)槟繕?biāo)SQL中相關(guān)表的統(tǒng)計(jì)信息不準(zhǔn)。比如三個(gè)表T1、T2和T3做表連接，T1和T2的連接條件為T1.ID1=T2.ID1，T2和T3的連接條件為T2.ID2=T3.ID2，同時(shí)在表T2的連接列ID1和ID2上存在一個(gè)包含這兩個(gè)連接列的組合索引。如果表T1和T3的統(tǒng)計(jì)信息不準(zhǔn)，導(dǎo)致Oracle認(rèn)為表T1和T3都只有很少量的記錄(比如都只有1條記錄)，則此時(shí)Oracle很可能會(huì)選擇先對(duì)表T1和T3做笛卡兒連接，然后再和表T2做表連接。因?yàn)?/span>Oracle認(rèn)為表T1和T3做笛卡兒連接后連接結(jié)果集的Cardinality的值是1，并且連接結(jié)果中間會(huì)同時(shí)包含列ID1和列ID2，這意味著此時(shí)Oracle就可以利用表T2中的上述組合索引了。這種笛卡兒連接通常是有問(wèn)題的，如果表T1和T3的實(shí)際記錄數(shù)并不都是1，而全部是1000，那么此時(shí)表T1和表T3做笛卡兒連接的結(jié)果集的Cardinality的值將是100萬(wàn)，顯然這種情況下如果還是按照笛卡兒連接的方式來(lái)執(zhí)行的話，則該SQL的執(zhí)行效率就會(huì)受到嚴(yán)重影響。

參考《基于Oracle的SQL優(yōu)化》