ORACLE3常用聯(lián)接方式

1、嵌套循環(huán)聯(lián)接嵌套循環(huán)聯(lián)接也稱為嵌套迭代，它將一個聯(lián)接輸入用作外部輸入表（顯示為圖形執(zhí)行計劃中的頂 端輸入），將另一個聯(lián)接輸入用作內(nèi)部（底端）輸入表。外部循環(huán)逐行消耗外部輸入表。內(nèi)部循 環(huán)為每個外部行執(zhí)行，在內(nèi)部輸入表中搜索匹配行。最簡單的情況是，搜索時掃描整個表或索引； 這稱為單純嵌套循環(huán)聯(lián)接。如果搜索時使用索引，則稱為索引嵌套循環(huán)聯(lián)接。如果將索引生成為 查詢計劃的一部分（并在查詢完成后立即將索引破壞），則稱為臨時索引嵌套循環(huán)聯(lián)接。查詢優(yōu) 化器考慮所有這些不同形式。如果外部輸入很小而內(nèi)部輸入很大且預(yù)先創(chuàng)建了索引，則嵌套循環(huán) 聯(lián)接尤其有效。在許多小事務(wù)中（如那些只影響較小的一組行的事務(wù)），索引嵌

2、套循環(huán)聯(lián)接遠比 合并聯(lián)接和哈希聯(lián)接優(yōu)越。但在大查詢中，嵌套循環(huán)聯(lián)接通常不是最佳選擇。合并聯(lián)接 sort merge join合并聯(lián)接要求兩個輸入都在合并列上排序，而合并列由聯(lián)接謂詞的等效（WHERE）子句定義。 查詢優(yōu)化器一般掃描索引（如果在適當(dāng)?shù)囊唤M列上存在一個索引），或在合并聯(lián)接的下面放一個 排序運算符。在很少的情況下，雖然可能有多個等效子句，但只用其中一些可用的等效子句獲得 合并列。由于每個輸入都已排序，Merge Join運算符將獲取每個輸入中的行并將其進行比較。例如，對 于內(nèi)聯(lián)接操作，如果行相等則返回。如果行不相等，則廢棄值較小的行并從該輸入中獲得另一行。 這一過程將重復(fù)進行，直到處

3、理完所有的行為止。合并聯(lián)接操作可以是常規(guī)操作，也可以是多對多操作。多對多合并聯(lián)接使用臨時表存儲行。如果 每個輸入中有重復(fù)值，則在處理其中一個輸入中的每個重復(fù)項時，另一個輸入必須重繞到重復(fù)項 的開始位置。如果存在駐留謂詞，所有滿足合并謂詞的行都將對該駐留謂詞取值，然后只返回那些滿足該駐留 謂詞的行。合并聯(lián)接本身的速度很快，但如果需要排序操作，選擇合并聯(lián)接就會非常費時。然而，如果數(shù)據(jù) 量很大且能夠從現(xiàn)有B樹索引中獲得預(yù)排序的所需數(shù)據(jù)，則合并聯(lián)接通常是最快的可用聯(lián)接算 法。哈希聯(lián)接哈希聯(lián)接有兩種輸入：生成輸入和探測輸入。查詢優(yōu)化器指派這些角色，使兩個輸入中較小的那 個作為生成輸入。哈希聯(lián)接可用于許多

4、類型的集合匹配操作：內(nèi)聯(lián)接，左向外聯(lián)接、右向外聯(lián)接和完整外聯(lián)接，左 向半聯(lián)接和右向半聯(lián)接、交集、聯(lián)合和差分。而且，哈希聯(lián)接的變化形式能夠進行重復(fù)項刪除和 分組操作（如SUM（salary） GROUP BY department）。這些修改對生成和探測角色只使用一個 輸入。與合并聯(lián)接相似，只有當(dāng)聯(lián)接謂詞中至少有一個等效（WHERE）子句時才能使用哈希聯(lián)接。然 而，聯(lián)接一般用于重組合由主鍵和外鍵之間的等效謂詞表達的關(guān)系，因此大多數(shù)聯(lián)接至少有一個 等效子句。用等效謂詞表達的列集合稱為哈希鍵，因為這些列有助于哈希函數(shù)。還可以有附加的 謂詞，并且可以將這些謂詞取值為駐留謂詞以與哈希值比較分開。哈希鍵可

5、以是表達式，只要能 從單個行中的列對其進行排它計算。在分組操作中，按列表分組的列是哈希鍵。在交集等集合操 作中以及刪除復(fù)制項時，哈希鍵由所有列組成。內(nèi)存中的哈希聯(lián)接哈希聯(lián)接先掃描或計算整個生成輸入，然后在內(nèi)存中生成哈希表。根據(jù)為哈希鍵計算出的哈 希值，將每行插入哈希存儲桶。如果整個生成輸入比可用內(nèi)存少，則可以將所有行都插入哈 希表中。生成階段后接著是探測階段。一次一行地對整個探測輸入進行掃描或計算，并為每 個探測行計算哈希鍵的值，掃描相應(yīng)的哈希存儲桶并生成匹配項。Grace哈希聯(lián)接如果生成輸入不適合內(nèi)存，哈希聯(lián)接將分步進行。每一步都包括生成階段和探測階段。首先， 消耗整個生成和探測輸入并（使用

6、哈希鍵上的哈希函數(shù)）將其分區(qū)為多個文件。這類文件的 數(shù)目稱為分區(qū)輸出端。通過使用哈希鍵上的哈希函數(shù)，可以保證任意兩個聯(lián)接記錄必在相同 的文件對中。因此，聯(lián)接兩個大輸入的任務(wù)簡化為相同任務(wù)的多個較小的實例。然后將哈希 聯(lián)接應(yīng)用于每對分區(qū)文件。遞歸哈希聯(lián)接如果生成輸入非常大，以至于標(biāo)準(zhǔn)外部合并排序的輸入需要多個合并級別，則需要多個分區(qū) 步驟和多個分區(qū)級別。如果只有某些分區(qū)較大，則只需對這些分區(qū)使用附加的分區(qū)步驟。為 使所有的分區(qū)步驟盡可能快，將使用大的異步I/O操作以便單個線程就能使多個磁盤驅(qū)動 器繁忙工作。什么時候使用NL必須有一個table擁有index；只有一個table有index時，選擇

7、沒有index的table作為驅(qū)動表；當(dāng)兩個table都有index時，選擇結(jié)果集較小的table作為驅(qū)動表（CBO），RBO會選from的最后一個表做驅(qū)動表；適用于有index的連接，兩個有大小差異的結(jié)果集，數(shù)據(jù)量較??；對于被連接的數(shù)據(jù)子集較小的情 況，nested loop連接是個較好的選擇。nested loop就是掃描一個表，每讀到一條記錄，就 根據(jù)索引去另一個表里面查找，沒有索引一般就不會是nested loops。一般在nested loop中，驅(qū)動表滿足條件結(jié)果集不大，被驅(qū)動表的連接字段要有索引，這樣 就走nstedloop。如果驅(qū)動表返回記錄太多，就不適合nested loop

8、s 了。如果連接字段沒有索 引， 則適合走 hash join ， 因為不需要索引Merge Join合并列要求排序;在不使用index的情況下使用MR join：在連接table的字段上不存在可用的index；查詢將返回兩個table中大部分的數(shù)據(jù)塊；CBO 認為 table scan 比 index range scan 更少的 cost；適用于沒有index的連接，或兩個大小接近的超大結(jié)果集。sort merge join的操作通常分三步：對連接的每個表做table access full；對table access full 的結(jié)果進行排序；進行merge join對排序結(jié)果進行合并，

9、前兩步站很大的成本，如果行源已 經(jīng)被排過序那比hash join效率高hash join在oracle7.3中作為NL join的代替方式首次引入的。大小不同的結(jié)果集進行連接，小的結(jié)果集作為驅(qū)動表，創(chuàng)建基于內(nèi)存的Hash table，大的結(jié) 果集計算hash value0 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ū)都需要換 進內(nèi)存做hash joino這時候成本接近于全表掃描小表+分區(qū)數(shù)*全表掃描大表的代價和。至于兩個表都進行分區(qū)，其好處是可以使用parallel query，就是多個進程同時對不同的分 區(qū)進行join，然后再合并。但是復(fù)雜以下條件下hash join可能有優(yōu)勢：兩個巨大的表之間的連接。在一個巨大的表和一個小表之間的連接嵌套連接：驅(qū)動表小，內(nèi)表大切在關(guān)聯(lián)的字段上有索引；驅(qū)動表的字段逐一掃描內(nèi)表；返