# 迴圈方法簡介

### nested loops join

巢狀迴圈連線是一種兩個表做表連線時依靠兩層巢狀迴圈得到連線結果集的表連線方法。

巢狀迴圈連線的優缺點及適用場景：

- 如果驅動表所對應的驅動結果集的記錄數較少，同時在被驅動表的連線列存在唯一性索引（或者在被驅動表的連線列存在選擇性很好的非唯一性索引），此時巢狀迴圈連線的執行效率非常高；如果驅動表所對應的驅動結果集的記錄數很多，即使在被驅動表的連線列存在索引，此時使用巢狀迴圈連線的執行效率也不會高。
- 大表也可以做驅動表，關鍵在於目標SQL的謂詞條件能否將驅動結果集的數量降下來。
- 巢狀迴圈連線可以快速響應。排序合併連線要等到做完排序後合併操作時才能開始返回資料，雜湊連線要等到驅動結果集所對應的hash table全部建完後才能開始返回資料。

### sort merge join

排序合併連線是一種兩個表在做表連線時用排序操作（sort）和合並操作（merge）來得到連線結果集的表連線方法。

排序合併連線的優缺點及適用場景：

- 排序合併連線的執行效率不如雜湊連線，但是排序合併連線的使用範圍更廣，因為雜湊連線只用於等值連線，排序合併連線除等值連線還用於其他連線條件（、>=）。
- 排序合併連線不適合OLTP系統，對OLTP系統來說，排序是非常昂貴的操作。如果能避免排序操作，OLTP系統也可以使用排序合併連線。比如兩個表雖然是排序合併連線，實際上並不需要排序，因為這兩個表在各自的連線列都有索引。
- 排序合併連線不存在驅動表的概念。

### hash join

在 Oracle10g 及以後的資料庫版本中，優化器（實際上是 CBO，雜湊連線僅適用於 CBO）在解析目標 SQL 時是否考慮雜湊連線受限於隱含引數 \_HASH_JOIN_ENABLE，而在 Oracle10g 以前受限於隱含引數 HASH_JOIN_ENABLE。

USE_HASH Hint 的優先順序比引數 \_HASH_JOIN_ENABLE 高，即使 \_HASH_JOIN_ENABLE 引數值為 FALSE，CBO 解析 SQL 時仍使用雜湊連線。

雜湊連線的優缺點及適用場景：

- 雜湊連線不一定會排序，大多數情況下不需要排序。
- 雜湊連線的驅動表的連線列的可選擇性應儘可能好，可選擇性會影響 Hash Bucket 的記錄數，Hash Bucket 的記錄數會影響從該 Hash Bucket 中查詢匹配記錄的效率。
- 雜湊連線只適用於 CBO 和等值連線。
- 雜湊連線很適合於小表和大表之間做表連線而且連線結果集的記錄較多的情況，特別是小表的連線列的可選擇性非常好的情況，這時雜湊連線的直線時間近似於全表掃描大表的耗費時間。

# 執行計劃相關說明

- NESTED LOOPS
  通過巢狀迴圈獲取關聯資料。

在進行巢狀迴圈關聯時，第一個資料集是驅動資料集，即巢狀迴圈中的外迴圈

- NESTED LOOPS (ANTI)
  通過巢狀迴圈獲取關聯資料。

在 NESTED LOOPS (ANTI) 的過程中，如果發現外迴圈中讀取到的資料在內迴圈中能夠匹配到，則立即終止內迴圈、丟棄該資料，開始下一輪迴圈。

- NESTED LOOPS (SEMI)
  通過巢狀迴圈獲取不完整關聯資料。

在 NESTED LOOPS (SEMI) 的過程中，如果發現外迴圈中讀取到的資料在內迴圈中能夠匹配到，則立即終止內迴圈、返回該資料，開始下一輪迴圈。

- NESTED LOOPS (OUTER)
  通過巢狀迴圈進行外關聯，獲取關聯資料

我們通常說的關聯，如果沒有特別指明，都是說內關聯。而外關聯與內關聯的不同之處在於，無論在內迴圈中是否找到匹配資料，外迴圈中的資料都會被返回。

- NESTED LOOPS (PARTITION OUTER)
  以左外關聯的左邊資料集（或外關聯的右邊資料集）為外迴圈，將左外關聯的右邊資料集（或右外關聯的左邊資料集）分組（分割槽）進行外關聯匹配

> 提示：對於分割槽左（右）外關聯，從邏輯上看，左（右）表需要與右（左）表中的資料分組（分割槽）分別做外關聯。如果實際操作也按照這個邏輯實現，則意味著每次與一組資料進行關聯，都要讀取一次左（右）表資料。而在 NESTED LOOPS (PARTITION OUTER) 中，第一次讀取左（右）表資料後，就被快取在私有記憶體中，從而避免了多次重複讀取共享記憶體資料。

- HASH JOIN
  通過資料集的雜湊值匹配獲取關聯資料。

進行雜湊關聯時，先用雜湊函式對左資料集（也稱為構建資料集）按雜湊值分割槽，並且建立雜湊表，對映雜湊鍵值與分割槽；然後逐條掃描右資料集（也稱為勘測資料集）的資料記錄，用相同的雜湊函式獲取雜湊值，並與雜湊表進行匹配，找到相應的構建資料集的雜湊分割槽，然後再用分割槽中的資料進行精確匹配。

雜湊值與原始資料之間的關係是一對多的關係。即對於同一個雜湊演算法，一條原始資料只會有一個雜湊值，且多條不同資料的雜湊值可能相同。

- HASH JOIN (ANTI)
  通過資料集的雜湊值匹配獲取非關聯資料。

在匹配的過程中，如果雜湊值相同，且數值匹配，則立即終止對關聯雜湊表中剩餘雜湊值的匹配、丟棄該雜湊值，開始下一輪匹配。

> 提示：如果作為驅動的資料集的雜湊表非常大，以至於記憶體中雜湊空間無法一次性完成兩邊雜湊表的匹配，則會將驅動雜湊表分為一個小的雜湊表，一次匹配一個，其他的則暫時儲存到臨時表空間中。我們可以注意到上述執行計劃中有對臨時表空間的估算值

- HASH JOIN (RIGHT ANTI)
  取右邊資料集做驅動，通過資料集的雜湊值匹配獲取非關聯資料。

如果驅動資料集太大，以至於需要分配臨時空間暫存雜湊表，優化器則會考慮採用資料量較少的關聯資料集作為驅動。此時，就需要用 HASH JOIN (RIGHT ANTI) 進行關聯；同樣，在匹配的過程中，如果兩邊的雜湊值相同且數值匹配，則立即終止對關聯雜湊表中剩餘雜湊值的匹配、丟棄條數，開始下一輪匹配；不同的是，如果所有記錄都未匹配，則返回關聯資料集中的資料，而非驅動資料集的資料。

- HASH JOIN (ANTI SNA)
  通過資料集的雜湊值匹配獲取非關聯資料，並且同時關注是否有空值。這一操作在 11g 中引入。非關聯查詢時，是否進行檢測控制，可以由優化器引數 “_optimizer_null_aware_antijoin” 控制

- HASH JOIN (RIGHT ANTI SNA)
  取右邊資料集做驅動，通過資料集的雜湊值匹配獲取非關聯資料，同時關注是否有空值。這一操作在 11g 中引入。

- HASH JOIN (SEMI)
  通過資料集的雜湊值匹配獲取不完整關聯資料。

在匹配過程中，如果雜湊值相同、且數值匹配，則立即終止對關聯雜湊表中剩餘雜湊值的匹配、返回該雜湊值，開始下一輪匹配。

- HASH JOIN (RIGHT SEMI)
  取右邊資料集做驅動，通過資料集的雜湊值匹配獲取不完整關聯資料。

如果驅動資料集太大，為了避免讀寫臨時表空間，優化器會考慮通過 HASH JOIN (RIGHT SEMI) 進行關聯；同樣，在匹配過程中，如果雜湊值相同且數值匹配，則立即終止對關聯雜湊表中剩餘雜湊值的匹配、返回關聯資料集中的雜湊值，開始下一輪匹配；返回關聯資料集中的資料，而非驅動資料集的資料。

- HASH JOIN (OUTER)
  通過資料集的雜湊值匹配進行外（左）關聯資料關聯。

這裡的外關聯，實際上就是左外關聯。通過雜湊值匹配進行外關聯操作時，左資料集的資料無論是否匹配到右資料集，都會被獲取。

- HASH JOIN (RIGHT OUTER)
  通過資料集的雜湊值匹配進行右外關聯資料關聯。

- HASH JOIN (FULL OUTER)
  通過資料集的雜湊值匹配進行關聯資料完全關聯

- MERGE JOIN
  通過合併已排序的資料進行關聯。

進行合併關聯時，分別從兩個資料集的首位開始對資料進行比較，如果相等，則說明資料匹配，則對兩邊資料集的下一條資料進行比較；如果不相等，則獲取較小數值（如果為降序，則是大數值）所在資料集的下一條記錄；當兩邊資料集都存在多條相等的記錄時，需要在這多條記錄之間進行多重影匹配。

- SORT (JOIN)
  對資料進行排序，以執行合併關聯操作。

排序後的資料快取在私有記憶體中，因而可以減少對共享快取的訪問次數和鎖閥的爭用。

> 提示：在做合併關聯時，要求兩邊資料集已經排好序

- MERGE JOIN (ANTI)
  通過進行資料合併關聯獲取非關聯資料。

通過合併關聯獲取非關聯資料時，如果左指標指向的資料大於右邊資料，則有指標向後移一位，進行下一次比較；如果相等，兩邊指標都向後移一位；如果左邊資料小於右邊資料，則返回左資料（因為右邊不會存在與其相等的資料了），並且指標向後移動。

由於右資料集只是起到資料比較的作用，而不需要返回資料，因此會對右資料集進行唯一性排序，排除重複資料，以減少匹配次數。

- MERGE JOIN (SEMI)
  通過進行資料合併關聯獲取不完整關聯資料。

> MERGE JOIN (SEMI) 與 MERGE JOIN   不同之處在於，由於右資料集不需要返回資料，且與左資料集的資料記錄相等的資料只需要匹配一次，因此右資料集，會進行唯一性排序，以排除重複資料。

- MERGE JOIN (OUTER)
  通過進行資料合併關聯而進行（左）外關聯，獲取關聯資料。

通過合併關聯進行左外關聯時，當右邊資料記錄大於、等於左邊資料記錄時，都屬於匹配記錄。

- MERGE JOIN (PARTITION OUTER)
  將右邊資料集分組（分割槽），左資料集分別與每組資料進行合併外關聯，以獲取關聯資料。

- MERGE JOIN (CARTESIAN)
  通過合併關聯對資料集進行笛卡爾關聯。

進行笛卡爾關聯時，兩邊資料集中的任何一條資料記錄都會與關聯資料集中的任何一條記錄匹配，換句話說，它們之間不存在關聯條件，兩邊資料集的任何兩條記錄都滿足笛卡爾的關聯匹配要求。

- JOIN FILTER (CREATE)
  布隆過濾器：適用於判斷一個元素是否屬於一個資料集的資料結構。其基本思想就是用一個或多個雜湊函式對資料集中的每個成員做對映，對映結果不是存在完整的雜湊表中，而是一個向量中。位向量所有位初始都為 0，根據雜湊結果將位向量中的相應位置 1.對資料集中的所有成員的雜湊計算完成後，就得到了該資料集的位向量。當需要判斷一個元素是否屬於該資料集時，也用相同的雜湊函式對其對映得到它的位向量，然後將其位向量上所有為 1 的位與資料集位向量上相應位比較，如果發現資料集的位向量上某個位為 0，可以判斷這個元素不屬於該資料集。而如果所有相應位都為 1 的話，那麼該元素可能屬於這個資料集。

建立一個過濾器，以用於布隆過濾器

- JOIN FILTER (USE)
  使用系統建立的過濾器進行布隆過濾。

- PART JOIN FILTER (CREATE)
  在雜湊關聯時建立布隆過濾器，用於分割槽裁剪

- PARTITION HASH (JOIN-FILTER)
  利用布隆過濾，進行分割槽裁剪

# HINT

根據各類連表查詢的特性，可以使用 hint 來調整連表方式，從而起到優化的效果

### nested loops join

- LEADING Hint （指定 Nested Loop Join 順序）
- USE_NL (指定使用 Nested Loop Join)

**示例**

```sql
select /*+ leading(b a) USE_NL(a) */ * from t1 a, t2 b where a.c1=b.c1;
select /*+ leading(a b c) USE_NL(b c) */ * from t1 a, t2 b, t3 c where a.c1=b.c1 and a.c1=c.c1;
```

### hash join

- LEADING Hint （指定 Hash Join 順序）
- SWAP_JOIN_INPUTS Hint（指定 Build 表）
- NO_SWAP_JOIN_INPUTS Hint（指定 Probe 表）
- USE_HASH (指定使用 Hash Join)

**示例**

```sql
select /*+ leading(a b c) use_hash(b c) swap_join_inputs(b) */ * from t1 a, t2 b, t3 c where a.c1=b.c1 and a.c1=c.c1;
select /*+ leading(a b c) swap_join_inputs(b) swap_join_inputs(c) */ * from t1 a, t2 b, t3 c where a.c1=b.c1 and a.c1=c.c1;
select /*+ leading(a c b) USE_HASH(c b) swap_join_inputs(c) */ * from t1 a, t2 b, t3 c where a.c1=b.c1 and a.c1=c.c1;
select /*+ leading(c b a) USE_HASH(c b) */ * from t1 a, t2 b, t3 c where a.c1=b.c1 and a.c1=c.c1;
```

### sort merge join

- use_merge：在 Hint 的作用下，CBO 生成的執行計劃中使用 Merge Sort Join 連線方式。

**示例**

```sql
select /*+use_merge(a,b)*/* from t1 a, t2 b where a.c1=b.c1;
```

# 優化

1. 關聯方式的影響

- 使用等值連線，對於大資料量連表查詢，可以避免 nested loop，而使用 hash join

- 只有在等連線不可用的情況下事由區間連線
   - 避免使用 != 或者 not
   - 避免使用 LIKE 匹配

2. 表檢索集的影響

- 嘗試查詢特殊的列且列在小的數目中

- 過濾大表減少連線的行數，從過濾多的表向下查詢，儘可能的過濾大表
   - 除非很小的表，無論什麼情況多有索引
   - 使用分割槽將大表分割槽

3. 其它

- 檢索順序的影響
     ORACLE 在解析 SQL 語句的時候對 FROM 子句後面的表名是從右往左解析的，在對多表查詢中，一定要把小表寫在最右邊

- 檢索方式的影響
     
     可以使用 hint 來調整連線方式，大表關聯多用 hash join，有小表多用 nested loop，排序聚合多用 merge/sort join

# 參考

- 崔華：《基於Oracle的SQL優化》
- [SQL 執行計劃的固定手段 --- Hint 方式（Hash Join）](https://blog.csdn.net/weixin_50513167/article/details/115612034)
- [SQL 執行計劃的固定手段 --- Hint 方式（Nested Loop Join）](https://blog.csdn.net/weixin_50513167/article/details/115836410)

Oracle：優化方法總結（關於連表查詢）

相關文章