回顧下表的關(guān)聯(lián)方式:

? ? 自連接(自關(guān)聯(lián))

? ? 內(nèi)連接

? ? 外連接(左,右)

? ? 自然連接(natural join)

? ? 非等值連接

? ? 交叉連接(笛卡爾積)

JOIN 關(guān)鍵字用于將兩張表作連接，一次只能連接兩張表，JOIN 操作的各步驟一般是串行的（在讀取做連接的兩張表的數(shù)據(jù)時可以并行讀取）；

表（row source）之間的連接順序?qū)τ诓樵冃视泻艽蟮挠绊?，對首先存取的表（?qū)動表）先應(yīng)用某些限制條件（Where過濾條件）以得到一個較小的row source，可以使得連接效率提高。

驅(qū)動表（Driving Table）與匹配表（Probed Table）

驅(qū)動表（Driving Table）：

表連接時首先存取的表，又稱外層表（Outer Table），這個概念用于 NESTED LOOPS（嵌套循環(huán)） 與 HASH JOIN（哈希連接）中；

如果驅(qū)動表返回較多的行數(shù)據(jù)，則對所有的后續(xù)操作有負(fù)面影響，故一般選擇小表（應(yīng)用Where限制條件后返回較少行數(shù)的表）作為驅(qū)動表。

匹配表（Probed Table）：

又稱為內(nèi)層表（Inner Table），從驅(qū)動表獲取一行具體數(shù)據(jù)后，會到該表中尋找符合連接條件的行。故該表一般為大表（應(yīng)用Where限制條件后返回較多行數(shù)的表）。

表連接的幾種方式: ?

Sort Merge Join (排序-合并連接):

假設(shè)有查詢：select a.name, b.name from table_A a join table_B b on (a.id = b.id)

內(nèi)部連接過程：

? ? a) 生成 row source 1 需要的數(shù)據(jù)，按照連接操作關(guān)聯(lián)列（如示例中的a.id）對這些數(shù)據(jù)進(jìn)行排序

? ? b) 生成 row source 2 需要的數(shù)據(jù)，按照與 a) 中對應(yīng)的連接操作關(guān)聯(lián)列（b.id）對數(shù)據(jù)進(jìn)行排序

? ? c) 兩邊已排序的行放在一起執(zhí)行合并操作（對兩邊的數(shù)據(jù)集進(jìn)行掃描并判斷是否連接）

延伸：

如果示例中的連接操作關(guān)聯(lián)列 a.id，b.id 之前就已經(jīng)被排過序了的話，連接速度便可大大提高，因?yàn)榕判蚴呛苜M(fèi)時間和資源的操作，尤其對于有大量數(shù)據(jù)的表。

故可以考慮在 a.id，b.id 上建立索引讓其能預(yù)先排好序。不過遺憾的是，由于返回的結(jié)果集中包括所有字段，所以通常的執(zhí)行計劃中，即使連接列存在索引，也不會進(jìn)入到執(zhí)行計劃中，除非進(jìn)行一些特定列處理（如僅僅只查詢有索引的列等）。

排序-合并連接的表無驅(qū)動順序，誰在前面都可以；

排序-合并連接適用的連接條件有： < ? <= ? = ? > ? >= ，不適用的連接條件有： `< >` ? like

Nested Loops (嵌套循環(huán)):

內(nèi)部連接過程：

? ? a) 取出 row source 1 的 row 1（第一行數(shù)據(jù)），遍歷 row source 2 的所有行并檢查是否有匹配的，取出匹配的行放入結(jié)果集中

? ? b) 取出 row source 1 的 row 2（第二行數(shù)據(jù)），遍歷 row source 2 的所有行并檢查是否有匹配的，取出匹配的行放入結(jié)果集中

? ? c) ……

若 row source 1 （即驅(qū)動表）中返回了 N 行數(shù)據(jù)，則 row source 2 也相應(yīng)的會被全表遍歷 N 次。

因?yàn)?row source 1 的每一行都會去匹配 row source 2 的所有行，所以當(dāng) row source 1 返回的行數(shù)盡可能少并且能高效訪問 row source 2（如建立適當(dāng)?shù)乃饕r，效率較高。

延伸：

嵌套循環(huán)的表有驅(qū)動順序，注意選擇合適的驅(qū)動表。

嵌套循環(huán)連接有一個其他連接方式?jīng)]有的好處是：可以先返回已經(jīng)連接的行，而不必等所有的連接操作處理完才返回數(shù)據(jù)，這樣可以實(shí)現(xiàn)快速響應(yīng)。

應(yīng)盡可能使用限制條件（Where過濾條件）使驅(qū)動表（row source 1）返回的行數(shù)盡可能少，同時在匹配表（row source 2）的連接操作關(guān)聯(lián)列上建立唯一索引（UNIQUE INDEX）或是選擇性較好的非唯一索引，此時嵌套循環(huán)連接的執(zhí)行效率會變得很高。若驅(qū)動表返回的行數(shù)較多，即使匹配表連接操作關(guān)聯(lián)列上存在索引，連接效率也不會很高。

Hash Join (哈希連接):

哈希連接只適用于等值連接（即連接條件為 ?= ?）

HASH JOIN對兩個表做連接時并不一定是都進(jìn)行全表掃描，其并不限制表訪問方式；

內(nèi)部連接過程簡述：

? ? a) 取出 row source 1（驅(qū)動表，在HASH JOIN中又稱為Build Table） 的數(shù)據(jù)集，然后將其構(gòu)建成內(nèi)存中的一個 Hash Table（Hash函數(shù)的Hash KEY就是連接操作關(guān)聯(lián)列），創(chuàng)建Hash位圖（bitmap）

? ? b) 取出 row source 2（匹配表）的數(shù)據(jù)集，對其中的每一條數(shù)據(jù)的連接操作關(guān)聯(lián)列使用相同的Hash函數(shù)并找到對應(yīng)的key里的數(shù)據(jù)在 Hash Table 中的位置，在該位置上檢查能否找到匹配的數(shù)據(jù)

HASH JOIN的三種模式：

? ? OPTIMAL HASH JOIN

? ? ONEPASS HASH JOIN

? ? MULTIPASS HASH JOIN

1. OPTIMAL HASH JOIN

? ?OPTIMAL 模式是從驅(qū)動表（也稱Build Table）上獲取的結(jié)果集比較小，可以把根據(jù)結(jié)果集構(gòu)建的整個Hash Table都建立在用戶可以使用的內(nèi)存區(qū)域里。

連接過程簡述：

? ? ? ? Ⅰ：首先對Build Table內(nèi)各行數(shù)據(jù)的連接操作關(guān)聯(lián)列使用Hash函數(shù)，把Build Table的結(jié)果集構(gòu)建成內(nèi)存中的Hash Table。如圖所示，可以把Hash Table看作內(nèi)存中的一塊大的方形區(qū)域，里面有很多的小格子，Build Table里的數(shù)據(jù)就分散分布在這些小格子中，而這些小格子就是Hash Bucket。

? ? ? ? Ⅱ：開始讀取匹配表（Probed Table）的數(shù)據(jù)，對其中每行數(shù)據(jù)的連接操作關(guān)聯(lián)列都使用同上的Hash函數(shù)，定位Build Table里使用Hash函數(shù)后具有相同值數(shù)據(jù)所在的Hash Bucket。

? ? ? ? Ⅲ：定位到具體的Hash Bucket后，先檢查Bucket里是否有數(shù)據(jù)，沒有的話就馬上丟掉匹配表（Probed Table）的這一行。如果里面有數(shù)據(jù)，則繼續(xù)檢查里面的數(shù)據(jù)（驅(qū)動表的數(shù)據(jù)）是否和匹配表的數(shù)據(jù)相匹配。

2. ONEPASS HASH JOIN

? ? 從驅(qū)動表（也稱Build Table）上獲取的結(jié)果集較大，無法將根據(jù)結(jié)果集構(gòu)建的Hash Table全部放入內(nèi)存中時，會使用 ONEPASS 模式。

連接過程簡述：

? ? ? ? Ⅰ：對Build Table內(nèi)各行數(shù)據(jù)的連接操作關(guān)聯(lián)列使用Hash函數(shù)，根據(jù)Build Table的結(jié)果集構(gòu)建Hash Table后，由于內(nèi)存無法放下所有的Hash Table內(nèi)容，將導(dǎo)致有的Hash Bucket放在內(nèi)存里，有的Hash Bucket放在磁盤上，無論放在內(nèi)存里還是磁盤里，Oracle都使用一個Bitmap結(jié)構(gòu)來反映這些Hash Bucket的狀態(tài)（包括其位置和是否有數(shù)據(jù)）。

? ? ? ? Ⅱ：讀取匹配表數(shù)據(jù)并對每行的連接操作關(guān)聯(lián)列使用同上的Hash函數(shù)，定位Bitmap上Build Table里使用Hash函數(shù)后具有相同值數(shù)據(jù)所在的Bucket。如果該Bucket為空，則丟棄匹配表的這條數(shù)據(jù)。如果不為空，則需要看該Bucket是在內(nèi)存里還是在磁盤上。

? ? ? ? 如果在內(nèi)存中，就直接訪問這個Bucket并檢查其中的數(shù)據(jù)是否匹配，有匹配的話就返回這條查詢結(jié)果。

? ? ? ? 如果在磁盤上，就先把這條待匹配數(shù)據(jù)放到一邊，將其先暫存在內(nèi)存里，等以后積累了一定量的這樣的待匹配數(shù)據(jù)后，再批量的把這些數(shù)據(jù)寫入到磁盤上（上圖中的 Dump probe partitions to disk）。

? ? ? ? Ⅲ：當(dāng)把匹配表完整的掃描了一遍后，可能已經(jīng)返回了一部分匹配的數(shù)據(jù)了。接下來還有Hash Table中一部分在磁盤上的Hash Bucket數(shù)據(jù)以及匹配表中部分被寫入到磁盤上的待匹配數(shù)據(jù)未處理，現(xiàn)在Oracle會把磁盤上的這兩部分?jǐn)?shù)據(jù)重新匹配一次，然后返回最終的查詢結(jié)果。

3. MULTIPASS HASH JOIN

? ? 當(dāng)內(nèi)存特別小或者相對而言Hash Table的數(shù)據(jù)特別大時，會使用 MULTIPASS 模式。MULTIPASS會多次讀取磁盤數(shù)據(jù)，應(yīng)盡量避免使用該模式。

Cartesian Product (笛卡爾積):

兩表的相乘