Oracle優化器的基礎知識(二)

一、 表連接配接

      顧名思義，表連接配接就是指多個表之間用連接配接條件連接配接在一起，使用表連接配接的目标SQL的目的就是從多個表擷取存儲在這些表中的不同次元的資料。展現在SQL語句上，含表連接配接的目标SQL的from部分會出現多個表，而這些SQL的where條件部分則會定義具體的表連接配接條件。

      當優化器解析含表連接配接的目标SQL時，它除了會根據目标SQL的SQL文本的寫法來決定表連接配接的類型之外，還必須決定如下三件事情才能得到最終的執行計劃。

1.表連接配接順序

      不管目标SQL中有多少個表做表連接配接，Oracle在實際執行該SQL時都隻能先兩兩做表連接配接，再依次執行這樣的兩兩表連接配接過程，直到目标SQL中所有的表都已連接配接完畢。從嚴格意義上來說，這裡的表連接配接順序包含兩層含義：一層含義是當兩個表做表連接配接時，優化器需要決定這兩個表中誰是驅動表(outer table)，誰是被驅動表(inner table)；另外一層含義是當多表(超過兩個以上的表)做表連接配接時，優化器需要決定這些表中誰和誰先做表連接配接，然後決定這個表連接配接結果所在的結果集再和剩餘表中的哪一個再做表連接配接，這個兩兩表連接配接的過程會一直持續下去，直到目标SQL中所有的表都已經連接配接完為止。

2.表連接配接方法

      在Oracle資料庫中，兩個表之間的表連接配接方法有排序合并(sort merge join)、嵌套循環連接配接(nested loops join)、哈希連接配接(hash join)和笛卡兒連接配接(cross join)這4種，是以優化器在解析含表連接配接的目标SQL時，都需要從上述四種方法中選擇一種，作為每一對表兩兩做表連接配接時所需要采用的方法。

3.通路單表的方法

      對于優化器而言，僅決定表連接配接順序和表連接配接方法是不夠的，這還不中以得到目标SQL的最終執行計劃，因為優化器在對目标SQL中的各個表兩兩做表連接配接時，還必須決定如何去擷取存儲在這些表裡的不同次元的資料，即優化器還要決定通路單表的方法。比如在通路某個單表時，是采用全表掃描還是走索引，如果是走索引，應該采用什麼樣的索引通路方法等。

1.1 表連接配接的類型

通常情況下，我們可以認為Oracle資料庫中的表連接配接分為内連接配接和外連接配接這兩種類型，表連接配接的類型會直接決定表連接配接的結果，而目标SQL的SQL文本的寫法又直接決定了表連接配接的類型。

1.1.1 内連接配接

内連接配接(Inner Join)是指表連接配接的結果隻包含那些完全滿足連接配接條件的記錄。對于包含表連接配接的目标SQL而言，隻要其where條件中沒有寫那些标準SQL中定義或者Oracle中自定義的表示外連接配接的關鍵字(比如标準SQL中的left outer join、right outer join、full outer join，或者Oracle中自定義的用來表示外連接配接的關鍵字“(+)”)，則該SQL的連接配接類型就是内連接配接。

Oracle自定義的内連接配接寫法：

      目标表1,目标表2 where 連接配接條件

标準SQL中内連接配接是用JOIN ON或者JOIN USING。

   JOIN ON的文法：

      目标表1 join 目标表2 on (連接配接條件)

   JOIN USING的文法：

      目标表1 join 目标表2 using(連接配接列集合)。

對于使用JOIN USING的目标SQL而言，如果有多個連接配接列，其文法中“(連接配接列集合)”裡的各個連接配接列之間應使用逗号來分隔。需要注意的時，使用JOIN USING的連接配接文法，如果連接配接列同時又出現在查詢列中，則該連接配接列前不能帶上表名或者表名的别名(alias),否則Oracle會報錯(ORA-25154)。

标準SQL中還有一種特殊的JOIN USING，我們稱之為NATURAL JOIN，其含義是使用NATURAL JOIN的表連接配接的連接配接列是表連接配接的兩個表所有的同名列。文法：

      目标表1 natural join 目标表2

這實際相當于目标表1 join 目标表2 using(目标表1和目标表2的所有同名列集合)。使用NATURAL JOIN好外是無須寫連接配接列集合，但其壞處是增加了表連接配接的執行結果出錯的風險，因為兩個表之間的同名列不一定在含義上就完全相同，也許隻是恰好同名，而即使含義相同，也不一定就需要将它們作為連接配接列。

1.1.2 外連接配接

      外連接配接(Outer Join)是對内連接配接的一種擴充，它是指表連接配接的連接配接結果除了包含那些完全滿足連接配接條件的記錄之外還會包含驅動表中所有不滿足該條件的記錄。

      标準SQL中的外連接配接分為左外連接配接(Left Outer Join)、右連接配接(Right Outer Join)和全連接配接(Full Outer Join)這三種，它們在标準SQL中對應的關鍵字分别為left outer join、right outer join、full outer join，都可以和JOIN ON 或JOIN USING連用。

左連接配接的文法：

      目标表1 left outer join 目标表2 on(連接配接條件) 或目标表1 left outer join 目标表2 using (連接配接列集合)

      其含義是目标表1和目标表2按括号中的連接配接條件來做表連接配接，位于關鍵字左邊的表1作為驅動表(outer table)，此時的連接配接結果包含了表1和表2中所有滿足該連接配接條件的記錄外，還會包含驅動表(表1)中所有不滿足該連接配接條件的記錄，同時，驅動表中所有不滿足該連接配接條件的記錄所對應的被驅動表(表2)中的查詢列均會以NULL值來填充。

右連接配接的文法：

      目标表1 right outer join 目标表2 on(連接配接條件) 或目标表1 right outer join 目标表2 using (連接配接列集合)

      含義與左連接配接相似，不過，這次位于關鍵字右表的表2為驅動表。

全連接配接文法：

      目标表1 full outer join 目标表2 on(連接配接條件) 或目标表1 full outer join 目标表2 using (連接配接列集合)

      其含義是目标表1和目标表2按括号中的連接配接條件來做表連接配接。此時的連接配接結果除了包含表1和表2中所有滿足該連接配接條件的記錄外，還會包含目标表1和目标表2中所有不滿足該連接配接條件的記錄，同時，表1和表2中所有不滿足該連接配接條件的記錄所對就的另外一個表中的查詢列均會以NULL值來填充。

      上面介紹的範例SQL中除了帶連接配接條件外，并沒有帶其他額外的限制條件。如果目标SQL中除了表連接配接條件之外還帶了額外的限制條件，則目标SQL中表連接配接的類型和該額外限制條件在目标SQL的SQL文本中出現的位置都可能會對最終執行結果産生影響。

      内連接配接添加其他限制條件執行個體：

<a href="https://s3.51cto.com/wyfs02/M02/8D/8F/wKioL1ihrk-SxgXCAAAWlG7gkQI110.png" target="_blank"></a>

      對内連接配接而言，除了表連接配接條件之外的額外限制條件在目标SQL的SQL文本中所處的位置不會影響該SQL的實際執行結果。

      外連接配接添加其他限制條件執行個體：

<a href="https://s1.51cto.com/wyfs02/M00/8D/8F/wKioL1ihrnHh4CwZAAAb2Gbvl60191.png" target="_blank"></a>

      對于外連接配接而言，如果額外限制條件在外連接配接關鍵字對應的括号内，這表示該限制條件會在表t1和表t2做右連接配接之前就被應用在表t1上，而如果額外限制條件在外連接配接關鍵字對應的括号外，表示該限制條件在表t1和表t2做完右連接配接後，才會被應用在表t1和表t2的連接配接結果集上。

      是以，對于外連接配接而言，除了表連接配接條件之外的額外限制條件在目标SQL的SQL文本中所處的位置确實可能會影響該SQL的實際執行結果。

      和标準SQL裡表示外連接配接的文法不同，Oracle用自定義的關鍵字“(+)”來表示外連接配接。關鍵字“(+)”的位置在目标SQL連接配接條件中某一個表的連接配接列後面，其含義是關鍵字“(+)”出現在哪個表的連接配接列後面，就表明哪個表會以NULL值來填充那不滿足連接配接條件找位置該表中的查詢列，此時應該以關鍵字“(+)”對應的表作為外連接配接的驅動表，這是的關鍵是哪個表是驅動表！

      之前提到過：對于外連接配接而言，表連接配接條件之外的額外限制條件在目标SQL的SQL文本中所處位置的不同可能會影響該SQL的實際執行結果。那如果使用Oracle自定義的關鍵字“(+)”來表示外連接配接的話，那麼如何展現呢？很簡單，Oracle是通過在額外限制條件的目标列的後面帶上同樣的關鍵字“(+)”來展現出上述影響的：

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

from t1,t2

where t1.col2(+)=t2.col2

and t1.col1(+)=1;

      前面提到的NATURAL JOIN不僅适用于内連接配接，也同樣适用于外連接配接：

select t1.col1,col2,t2.col3

from t1 natural left outer join t2 ;

1.2 表連接配接的方法

      之前介紹過，優化器在解析含表連接配接的目标SQL時，當它根據目标SQL的SQL文本的寫法決定表連接配接的類型之後，接下來要做的事情之一就是決定表連接配接的方法。

      在Oracle資料庫中，兩個表之間的表連接配接方法有排序合并連接配接、嵌套循環連接配接、哈希連接配接和笛卡兒連接配接這四種。這四種表連接配接各有優缺點，也各有其适用場景，接下來分别介紹它們

1.2.1 排序合并連接配接

      排序合并連接配接(Sort Merge Join)是一種兩個表在做表連接配接時用排序操作(Sort)和合并操作(Merge)來得到連接配接結果集的表連接配接方法。

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

首先以目标SQL中指定的謂詞條件(如果有的話)去通路表T1，然後對通路結果按照表T1中的連接配接來排序，排好序後的結果集我們記為結果集1。

接着以目标SQL中指定的謂詞條件(如果有的話)去通路表T2，然後對通路結果按照表T2中的連接配接來排序，排好序後的結果集我們記為結果集2。

最後對結果集1和結果集2執行合并操作，從中取出比對記錄來作為排序合并連接配接的最終執行結果。

      對于排序合并連接配接的優缺點及适用場景，總結如下：

通常情況下，排序合并連接配接的執行效率會遠不如哈希連接配接，但前者的使用範圍更廣，因為哈希連接配接通常隻能用于等值連接配接，而排序合并連接配接并不能用于其他條件(例如&lt;、&lt;=、&gt;、&gt;=)。

通常情況下，排序合并連接配接并不短途OLTP類型的系統，其本質原因是因為對OLTP類型的系統而言，排序是非常昂貴的操作，當然，如果能避免排序操作，那麼即使是OLTP類型的系統，也還是可以使用排序合并連接配接的。比如兩個表雖然是排序合并連接配接，但實際上它們并不需要排序，因為這兩個表各自的連接配接列上都存在索引。

從嚴格意義上說，排序合并連接配接不存在驅動表的概念。

1.2.2 嵌套循環連接配接

      嵌套循環連接配接(Nested Loops Join)是一種兩個表在做表連接配接時依靠兩層嵌套循環(分别為外層循環和内層循環)來得到連接配接結果集的表連接配接方法。

      如果兩個表(假如為T1和T2)在做表連接配接時使用的是嵌套循環連接配接，則Oracle會依次順序執行如下步驟：

首先，優化器會按照一定的規則來決定表T1和T2中誰是驅動表、誰是被驅動表。驅動表用于外層循環，被驅動表用于記憶體循環。這是假設驅動表是T1，被驅動表是T2。

接着以目标SQL中指定的謂詞條件(如果有的話)去通路驅動表T1，通路驅動表T1後得到的結果集我們記為驅動結果集1。

然後周遊驅動結果集1并同時周遊被驅動表T2，即先取出驅動結果集1中的第1條記錄，接着周遊被驅動表T2并按照連接配接條件去判斷T2中是否存在比對的記錄，然後再取出驅動結果集1中的第2條記錄，按照同樣的連接配接條件再去周遊被驅動表T2并判斷T2中是否還存在比對的記錄，直到周遊完驅動結果集1中所有的記錄為止。這裡的外層循環是指周遊驅動結果集1所對應的循環，内層循環是指周遊被驅動表T2所對應的循環。顯然，外層循環所對應的驅動結果集1有多少條記錄，周遊被驅動表T2的内層循環就要做多少次，這就是所謂的“嵌套循環”的含義。

      嵌套循環連接配接的優缺點及适用場景總結如下：

從上述嵌套循環連接配接的具體執行過程可以看出：如果驅動表所對應的驅動結果集的記錄數較少，同時被驅動表的連接配接列上又存在唯一性索引(或者在被驅動表的連接配接列上存在選擇性很的的非唯一性索引)，那麼此時使用嵌套循環連接配接的執行效率就會非常高；但如果驅動表所對應的驅動結果集的記錄數很多，即便在被驅動表的連接配接列上存在索引，此時使用嵌套循環連接配接的執行效率也不會高。

隻要驅動結果集的記錄數較少，那就具備了做嵌套循環連接配接的前提條件，而驅動結果集是在對驅動表應用了目标SQL中指定的謂詞條件(如果有的話)後所得到的結果集，是以大表也可以作為嵌套循環連接配接的驅動表，關鍵看目标SQL中指定的謂詞條件(如果有的話)能否将驅動結果集的資料量降下來。

嵌套循環連接配接有其他連接配接方法所沒有的一個優點：嵌套循環連接配接可以實作快速響應，即它可以第一時間傳回已經連接配接過具滿足連接配接條件的記錄，而不必等待所有的連接配接操作全部做完才傳回連接配接結果。雖然排序合并連接配接也可以，但它們并不是第一時間傳回，因為排序合并連接配接要等到排序完後做合并操作時才能開始傳回資料，而哈希連接配接則要等到驅動結果集所對應的Hash Table全部建完後才能開始傳回資料。

      如果Oracle使用的是嵌套循環連接配接，且在被驅動表的連接配接列上存在索引，那麼Oracle在通路索引時通常會使用單塊讀，這意味着嵌套循環連接配接的驅動結果集有多少條記錄，Oracle就會需要通路該索引多少次。另外，如果目标SQL中的查詢列并不能全部從驅動表的相關索引中獲得，那麼Oracle在做完嵌套循環連接配接後還需要對被驅動表執行回表操作。這個回青操作通常也會使用單塊讀，這意味着做完嵌套循環連接配接後的連接配接結果集有多少條記錄，Oracle就需要回表多少次。

      為了提高嵌套循環連接配接的執行效率，在Oracle 11g中，Oracle引入了向量I/O(Vector I/O)。在引入向量I/O後，Oracle就可以将原先一批單塊讀所需要耗費的實體I/O結合起來，然後用一個向量I/O去批處理它們，這樣就實作了在單塊讀的數量不降低的情況下減少這些單塊讀所需要耗費的實體I/O數量，也就提高了嵌套循環連接配接的執行效率。

1.2.3 哈希連接配接

      哈希連接配接(Hash Join)是一種兩個表在做表連接配接時主要依靠哈希運算來得到連接配接結果集的表連接配接方法。

      在Oracle7.3之前，Oracle資料庫中常用的表連接配接方法就隻有排序合并連接配接和嵌套循環連接配接這兩種，但這兩種方法都各有其明顯缺陷。對于排序合并連接配接，如果兩個表在施加了目标SQL中指定的謂詞條件(如果有的話)後得到的結果集很大且需要排序，則排序合并連接配接的執行效率一定不高；而對于嵌套循環連接配接，如果驅動表所對應的驅動結果集的記錄數很大，即便在被驅動表的連接配接列上存在索引，此時使用嵌套循環連接配接的執行效率也會同樣不高。為了上述情形下效率不高的問題，同時也為了給優化器提供一種新的選擇，Oracle在7.3中引入了哈希連接配接。從理論上來說，哈希連接配接的執行效率會比排序合并連接配接和嵌套循環連接配接要高，當然，實際情況并不總是這樣。

      在Oracle 10g及其以後的Oracle資料庫版本中，優化器(實際上是CBO，因為哈希連接配接僅适用于CBO)在解析目标SQL時是否考慮哈希連接配接是受限于隐含參數_HASH_JOIN_ENABLED，而在Oracle10g以前，CBO在解析目标SQL時是否考慮哈希連接配接是受限于參數HASH_JOIN_ENABLED。_HASH_JOIN_ENABLED的預設值是TRUE，表示允許CBO在解析目标SQL時考慮哈希連接配接。當然，即使該參數為FALSE，使用USE_HASH Hint依然可以讓CBO在解析目标SQL時考慮哈希連接配接，這說明USE_HASH Hint的優先級比參數_HASH_JOIN_ENABLED的優先級要高。

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

首先Oracle會根據參數HASH_AREAS_SIZE、DB_BLOCK_SIZE和_HASH_MULTIBLOCK_IO_COUNT的值來決定Hash Partition的資料(Hash Partition是一個邏輯上的概念，它實際上是一組Hash Bucket的集合。所有Hash Partition的集合就被稱為Hash Table，即一個Hash Table由多個Hash Partition所組成，而一個HashPartition又由多個Hash Bucket所組成的)。

表T1和T2在施加了目标SQL中指定的謂詞條件(如果有的話)後，得到的結果集中數量較少的那個結果集會被Oracle選為哈希連接配接的驅動結果集，這裡我們假設T1所對應的結果集的資料量相對較少，記為S；T2所對應的結果集的資料相對較多，記為B。顯然這裡S是驅動結果集，B是被驅動結果集。

接着Oracle會周遊S，讀取S中的每一條記錄，并對每一條記錄按照該記錄在表T1中的連接配接列做哈希運算。這個哈希運算會使用兩個内置哈希函數，這兩個哈希函數會同時對該連接配接列計算哈希值，我們把這兩個内置哈希函數分别記為hash_func_1和hash_func_2，它們所計算出來的哈希值分别記為hash_vale_1和hash_value_2。

然後Oracle會按照hash_value_1的值把相應的S中的對應記錄存儲在不同的Hash Partition的不同Hash Bucket裡，同時和該記錄存儲在一起的還有該記錄用hash_func_2計算出來的hash_value_2。注意，存儲在Hash Bucket裡的記錄并不是目标表的完整行記錄，隻需要存儲位置目标SQL中與目标表相關的查詢列和連接配接列就足夠了。我們把S所對應的每一個Hash Partition記為Si。

在建構Si的同時，Oracle會建構一個位圖(BITMAT)，這個位置用來标記Si所包含的每一個Hash Bucket是否記錄(即記錄數是否大于0)。

如果S的資料量很大，那麼在建構S所對應的Hash Table時，就可能會出現PGA的工作區(WORK AREA)被填滿的情況。這時候Oracle會把工作區中包含記錄數最多的Hash Partition寫到磁盤上(TEMP表空間)。接着Oracle會繼續建構S所對應的Hash Table，在繼續建構的過程中，如果工作區又滿了，則Oracle會繼續重複上述動作，即挑選包含記錄數最多的Hash Partition并寫回到磁盤上。如果要建構的記錄所對應的Hash Partition已經事先被Oracle寫回磁盤，則此時Oracle會去磁盤上更新Hash Partition ，即把該條記錄和Hash_vale_2直接加到這個已經位于磁盤上的Hash Partition的相應Hash Bucket中。注意，極端情況下可能會出現隻有某個Hash Partition的部分記錄不覺 在記憶體中，該Hash Partition的剩餘部分和餘下的所有Hash Partition都已經被寫回到磁盤上。

上述建構S所對應的Hash Table的過程會一直持續下去，直到周遊完S中的所有記錄為止。

接着，Oracle會對所有的Si按照它們所包含的記錄數來排序，然後把這些已經诽好序的Hash Partition按順序依次且盡可能全部放到記憶體中(PGA的工作區)，當然，如果實在放不下，放不下的那部分Hash Partition還是會位于磁盤上。

至此Oracle已經處理完S，現在可以開始處理B了。

Oracle會周遊B，讀取B中的每一條記錄，并按照該記錄在表T2中的連接配接列做哈希運算，這個哈希運算和步驟3中的哈希運算是一模一樣的，即還是會用步驟3中的hash_func_1和hash_func_2，并且也會計算出兩個哈希值hash_value_1和hash_value_2。

接着Oracle會按照該記錄所對應的哈希值hash_value_1去Si裡找比對的Hash Bucket中的每一條記錄的連接配接列，看是否是真的比對(即這裡要校驗S和B中比對記錄所對應的連接配接列是否真的相等，因為對于哈希運算而言，不同的值經過哈希運算後的結果可能是相同的)。如果真的比對，則上述hash_value_1所對應B中記錄的位于目标SQL中的查詢列和該Hash Bucket中的比對記錄便會組合起來，一起作為滿足目标SQL連接配接條件的記錄傳回。如果找不到比對的Hash Bucket，則Oracle就會去通路步驟5中建構的位圖。

如果位圖顯示該Hash Bucket在Si中對應的記錄數大于0，則說明該Hash Bucket雖然不在記憶體中，但它已經被寫回磁盤，此時Oracle就會按照hash_value_1的值把相應B中的對應記錄也可以Hahs Partition的方式寫回到磁盤上，同時和該記錄存儲在一起的還有該記錄用hash_func_2計算出來的hash_value_2的值。如果位圖顯示該Hash Bucket在Si中對應 的記錄數等于0，則Oralce就無須把上述hash_value_1所對應B中的記錄寫回磁盤了，因為這條記錄必須不滿足目标SQL的連接配接條件。這個根據位置來決定是否将hash_value_1所對就B的記錄寫回到磁盤的動作就是所謂的“位圖過濾”(Oralce不一定會啟用位圖過濾，因為如果所有的Si本來就都在記憶體中，也沒發生過将Si寫回到磁盤的操作，那麼這裡Oracle就不需要啟用位圖過濾了)。我們把B所對應的每一個Hash Partition記為Bj。

上述去Si中查找比對Hash Bucket和建構Bj的過程會一直持續下去，直到周遊完B中的所有記錄為止。

至此Oracle已經處理完所有位于記憶體中的Si和對應的Bj，現在隻剩下位于磁盤上的si和Bj還未處理。

因為在建構Si和Bj時用的是同樣的哈希函數hash_func_1和hash_func_2，是以Oracle在處理位于磁盤上的Si和Bj的時候可以放心地配對處理，即隻有對應Hash Partition     Number值相同的Si和Bj才可能會産生滿足連接配接條件的記錄。這裡我們用Sn和Bn來表示位于磁盤上且對應Hash Partition     Number值相同的Si和Bj。

對于每一對Sn和Bn，它們之中記錄數較少的會被當作驅動結果集，然後Oracle會用這個驅動結果集Hash Bucket裡的記錄的hash_vale_2來建構新的Hash Table，另外一個記錄數較多的會被當作被驅動結果集，然後Oracle會用這個被驅動結果集Hash Bucket裡記錄的hash_value_2去上述建構的新的Hash Table中找比對記錄。注意，對每一對Sn和Bn而言，Oracle始終會選擇它們中記錄數較少的來作為驅動結果集，是以每一對Sn和Bn的驅動結果集都可能會發生變化，這就是所謂的“動态角色互換”。

步驟14中如果存在比對記錄，則該比對記錄會作為滿足目标SQL連接配接條件的記錄傳回。

上述處理Sn和Bn的過程會一直持續下去，直到周遊完所有的Sn和Bn為止。

      哈希連接配接的優缺點及适用場景總結如下：

哈希連接配接不一定會排序，或者說大多數情況下都不需要排序。

哈希連接配接的驅動表所對應的連接配接列的可能性應盡可能好，因為這個可選擇性會影響對應Hash Bucket中的記錄數，而Hash Bucket中的記錄數又會直接影響從該Hash Bucket中查找比對記錄的效率。如果一個Hash Bucket裡所包含的記錄數過多，則可能會嚴重降低所對應哈希連接配接的執行效率，此時典型的表現就是該哈希連接配接執行了很長時間都沒有結束，資料庫所在資料庫伺服器上的CPU占用率很高，但目标SQL所消耗的邏輯讀卻很低，因為此時大部分時間都耗費在了周遊上述Hash Bucket裡的所有記錄上，而周遊Hash Bucket裡的記錄這個動作發生在PGA的工作區裡，是以不耗費邏輯讀。

哈希連接配接隻适用于CBO，它也隻能用于等值連接配接條件(即使是哈希反連接配接，Oracle實際上也是将其轉換成了等價的等值連接配接)。

哈希連接配接很适合于小表和大表之間做表連接配接且連接配接結果集的記錄數較多的情形，特别是在小表的連接配接列的可選擇性非常好的情況下，哈希連接配接的執行時間就可以近似看作是和全表掃描那個大表所耗費的時間相當。

當兩個表做哈希連接配接時，如果在施加了目标SQL中指定的謂詞條件(如果有的話)後得到的資料量較小的那個結果集所對應的Hash Table能夠完全被容納在記憶體(PGA的工作區)，則此時的哈希連接配接的執行效率會非常高。

1.2.4 笛卡兒連接配接

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

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

首先以目标SQL中指定的謂詞條件(如果有的話)通路表T1，此時得到的結果集我們記為結果集1，這裡假設結果集1的記錄數為m。

接着以目标SQL中指定的謂詞條件(如果有的話)通路表T2，此時得到的結果集我們記為結果集2，這裡假設結果集2的記錄數為n。

最後對結果集1和結果集2執行合并操作，從中取出比對記錄來作為笛卡兒連接配接的最終執行結果。這裡的特殊之處在于對于笛卡兒連接配接而言，因為淌有表連接配接條件，是以在對結果集1和結果集2執行合并操作時，對于結果集1中的任意一條記錄，結果集2中的所有記錄都滿足條件，即它們都會是比對記錄，是以上述笛卡兒連接配接的連接配接結果的記錄數就是m和n的乘積(即m×n)。

     語句示例：select t1.col1,t2.col3 from t1,t2;

      标準SQL用關鍵字“CROSS JOIN”來表示笛卡兒連接配接，如select t1.col1,t2.col3 from t1 cross join t2;

      對于笛卡兒連接配接的優缺點及适用場景總結如下：

笛卡兒連接配接的出現通常是由于目标SQL中漏寫了表連接配接條件，是以笛卡兒連接配接一般是不好的，除非刻意這樣做(比如有些情況下可以利用笛卡兒連接配接來減少對目标SQL中大表的全表掃描次數)。

有時候出現笛卡兒連接配接是因為目标SQL中使用了ORDERED Hint，同時在該SQL的SQL文本中位置相鄰的兩個表之間又沒有直接的關聯條件。

有時候出現笛卡兒連接配接是因為目标SQL中相關表的統計資訊不準。比如三個表T1、T2和T3做表連接配接，T1和T2的連接配接條件為T1.ID1=T2.ID1，T2和T3的連接配接條件為T2.ID2=T3.ID2，同時在表T2的連接配接列ID1和ID2上存在一個包含這兩個連接配接列的組合索引。如果表T1和T3的統計資訊不準，導緻Oracle認為表T1和T3都隻有很少量的記錄(比如都隻有1條記錄)，則此時Oracle很可能會選擇先對表T1和T3做笛卡兒連接配接，然後再和表T2做表連接配接。因為Oracle認為表T1和T3做笛卡兒連接配接後連接配接結果集的Cardinality的值是1，并且連接配接結果中間會同時包含列ID1和列ID2，這意味着此時Oracle就可以利用表T2中的上述組合索引了。這種笛卡兒連接配接通常是有問題的，如果表T1和T3的實際記錄數并不都是1，而全部是1000，那麼此時表T1和表T3做笛卡兒連接配接的結果集的Cardinality的值将是100萬，顯然這種情況下如果還是按照笛卡兒連接配接的方式來執行的話，則該SQL的執行效率就會受到嚴重影響。

參考《基于Oracle的SQL優化》

     本文轉自hbxztc 51CTO部落格，原文連結：http://blog.51cto.com/hbxztc/1897439，如需轉載請自行聯系原作者