DB2 優化器在執行連接查詢時，可選擇 巢狀迴圈連接、合併連接、雜湊連接 三種演算法。本文深入剖析三者運作機制、外部/內部表選擇邏輯、適用條件限制、記憶體/磁碟溢寫行為，並解析優化器基於成本模型的選擇策略，助您理解執行計畫、進行針對性調優。
一、通用概念：外部表與內部表
- 連接雙方必有一外部表、一內部表。
- 外部表僅掃描一次；內部表視演算法可能掃描多次。
- 優化器依成本模型（表大小、索引、緩衝區、謂詞、排序需求）決定誰為外部/內部表。
- 多表連接時，優化器每次僅連接兩表，產生中間結果集再繼續連接。
二、巢狀迴圈連接
運作流程
- 掃描外部表每一列。
- 對每一外部列，在內部表尋找匹配列：
- 全表掃描內部表：逐列比對（成本極高）。
- 索引查找：若連接欄位在內部表有索引，直接定位匹配列（大幅降低存取量）。
優化器選擇外/內表考量因子
- 表大小（小表優先做外部）
- 緩衝區命中率
- 連接謂詞選擇率
- 排序需求
- 內部表連接欄位是否有索引
- 連接欄位不可為 LONG/LOB
適用場景
- 外部表極小、內部表有高選擇率索引。
- 需保持外部表輸出順序。
三、合併連接
前置條件
- 至少一個等值連接謂詞（
table1.col = table2.col）。
- 雙方輸入依連接鍵已排序（利用索引順序掃描或顯式排序）。
- 連接欄位不可為 LONG/LOB。
運作流程
- 同步掃描兩表（類似合併排序的 merge 階段）。
- 外部表與內部表各掃描一次（除非外部表有重複值，需回溯內部表部分區段）。
- 優化器傾向選「重複值較少」的表為外部表，但最終仍以成本模型決定。
優勢
- 無需重複存取內部表，大表連接效能優於 NLJ。
- 輸出順序符合連接鍵排序，利於後續 ORDER BY 消除排序。
四、雜湊連接
前置條件
- 一或多個等值連接謂詞，對應欄位型態完全相同（CHAR 長度、DECIMAL 精度/小數位皆同）。
- 連接欄位不可為 LONG/LOB。
- 需設定環境變數啟用（
DB2_HASH_JOIN=ON 或資料庫配置）。
運作流程（兩階段）
- 建構階段：掃描內部表（建構表），依連接鍵計算雜湊碼，將列分散至記憶體雜湊分區。記憶體不足時，分區溢寫至磁碟臨時表。
- 探測階段：掃描外部表（探測表），對每列計算相同雜湊碼，定位對應分區比對實際連接鍵值。匹配分區在記憶體即時比對；在磁碟則將探測列亦寫入臨時表，最後處理臨時表完成匹配。
關鍵特性
- 支援多個等值謂詞（Merge Join 僅支援單一等值謂詞）—— 重大優勢。
- 優化器傾向選較小表為建構表，避免溢寫磁碟。
- 不保證輸出順序（溢寫磁碟會打亂順序），若需有序輸出需額外排序。
五、三大演算法選擇決策邏輯
| 場景 |
優先考慮 |
關鍵原因 |
| 小表驅動大表、大表有高選擇率索引 | NLJ | 索引查找成本極低 |
| 大表連接、雙方可利用索引順序/預排序 | Merge Join | 單掃描、無雜湊建構開銷、保持順序 |
| 大表連接、多等值條件、無索引順序、記憶體充足 | Hash Join | 多條件支援、CPU 密集但 I/O 少、平行化友善 |
| 需保持輸出順序、且大表無索引順序 | Merge Join / NLJ | Hash Join 可能溢寫破壞順序 |
六、調優實務建議
- 收集統計資料（
RUNSTATS），讓優化器擁有準確基數估計。
- 在連接欄位建立適當索引，啟用 NLJ/Merge Join 路徑。
- 大型數倉查詢：啟用 Hash Join（
DB2_HASH_JOIN=ON）、配置足夠 SORTHEAP/SHEAPTHRES 避免溢寫。
- 檢視執行計畫（
db2exfmt），確認實際選擇的演算法、外/內表角色、溢寫警告。
- 必要時用
OPTIMIZATION LEVEL 或 JOIN METHOD 提示強制指定（謹慎使用）。
小結
理解 NLJ、Merge Join、Hash Join 的底層機制與優化器決策邏輯，是 SQL 調優從「試錯」進階到「精準優化」的關鍵。善用統計資料、索引設計與記憶體配置，引導優化器選擇最適演算法，即可獲得穩定高效的連接效能。
