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

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

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

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

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

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

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

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

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

9、前兩步站很大的成本，如果行源已 經(jīng)被排過(guò)序那比hash join效率高h(yuǎn)ash join在oracle7.3中作為NL join的代替方式首次引入的。大小不同的結(jié)果集進(jìn)行連接，小的結(jié)果集作為驅(qū)動(dòng)表，創(chuàng)建基于內(nèi)存的Hash table，大的結(jié) 果集計(jì)算hash value0 hash join是CBO做大數(shù)據(jù)集連接時(shí)的常用方式。優(yōu)化器掃描小表（或 數(shù)據(jù)源），利用連接鍵（也就是根據(jù)連接字段計(jì)算hash值）在內(nèi)存中建立hash表，然后掃 描大表，每讀到一條記錄就來(lái)探測(cè)hash表一次，找出與hash表匹配的行。當(dāng)小表可以全部放入內(nèi)存中，其成本接近全表掃描兩個(gè)表的成本之和。如果表很大不能完全 放入內(nèi)存，這時(shí)優(yōu)化器會(huì)將它分割成若干不同的分區(qū)，不能放入內(nèi)存的部分就把該分區(qū)寫(xiě)入 磁盤(pán)的臨時(shí)段，此時(shí)要有較大的臨時(shí)段從而盡量提高I/O的性能。臨時(shí)段中的分區(qū)都需要換 進(jìn)內(nèi)存做hash joino這時(shí)候成本接近于全表掃描小表+分區(qū)數(shù)*全表掃描大表的代價(jià)和。至于兩個(gè)表都進(jìn)行分區(qū)，其好處是可以使用parallel query，就是多個(gè)進(jìn)程同時(shí)對(duì)不同的分 區(qū)進(jìn)行join，然后再合并。但是復(fù)雜以下條件下hash join可能有優(yōu)勢(shì)：兩個(gè)巨大的表之間的連接。在一個(gè)巨大的表和一個(gè)小表之間的連接嵌套連接：驅(qū)動(dòng)表小，內(nèi)表大切在關(guān)聯(lián)的字段上有索引；驅(qū)動(dòng)表的字段逐一掃描內(nèi)表；返