資料庫效能調優系列 (四)：深入理解 Hash Join (散列聯接) 執行計畫與代價模型

接續上篇 Nested Loops Join，本文深入剖析 **Hash Join (散列聯接)** — 大資料集聯接的利器。透過抽象代價模型 (假設緩衝區僅 1 頁) 推導 I/O 代價，對比 Nested Loops、Index Lookup、Hash Join 三者優劣，並以 Oracle 實測驗證優化器選擇邏輯。

為何需要 Hash Join？

兩表聯接 T1(c1) ⋈ T2(d1)，筆數皆 100 萬：

• Nested Loops (無索引)：100萬 × 100萬 = 10¹² 次比較 → **不可接受**
• Nested Loops (有索引)：外表 100萬 × (B-Tree 高度 4 + 1) = **500 萬頁 I/O**
• Hash Join：建構雜湊表 200萬 + 探查 200萬 = **400 萬頁 I/O** ⚡ **更勝一籌**

Hash Join 運作原理 (兩階段)

1. Build Phase (建構雜湊表)：全掃描 **Build Input (較小表)**，對聯接鍵計算雜湊值，將列分散存入雜湊桶 (記憶體/暫存庫)
2. Probe Phase (探查匹配)：全掃描 **Probe Input (較大表)**，同樣計算雜湊值，定位桶內候選列，逐一比對聯接條件，匹配則輸出

關鍵細節：雜湊衝突 (不同鍵值落同桶) 需 **精確比對** 確認；支援 **Grace Hash** (遞歸分割) 與 **Recursive Hash** (多層分割) 處理超大資料集。

抽象代價模型推導 (單位：頁 I/O)

結論：大表聯接無索引時，Hash Join 代價最低，甚至優於有索引的 Nested Loops。

Oracle 實測驗證

CREATE TABLE t1(c1 INT, c2 INT);
CREATE TABLE t2(d1 INT, d2 INT);
-- 各插入 10,000 筆
CREATE INDEX it1c1 ON t1(c1);

EXPLAIN PLAN FOR SELECT c1, d1 FROM t1 INNER JOIN t2 ON c1 = d1;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

-- 結果：
-- HASH JOIN (Cost=13)
--   TABLE ACCESS FULL T1
--   TABLE ACCESS FULL T2
-- 即使 t1(c1) 有索引，優化器仍選 Hash Join！

優化器選擇邏輯與開發者應對

• 統計資訊是關鍵：`DBMS_STATS.GATHER_TABLE_STATS` 確保基數估算準確
• 記憶體壓力：`PGA_AGGREGATE_TARGET` / `workarea_size_policy` 影響 Hash Join 是否溢寫暫存表空間
• Hint 強制：`/*+ USE_HASH(t1 t2) */` 或 `/*+ USE_NL(t1 t2) */` (測試用，生產慎用)
• 觀察執行計畫關鍵字：`HASH JOIN`、`BUFFERED` (需暫存)、`OPTIMAL` (全記憶體)

跨資料庫對應術語

實務建議

1. 大表聯接**優先檢查統計資訊**，而非盲目加索引
2. 記憶體充足時 Hash Join 效能極佳；記憶體受限注意 **TempDB/暫存表空間** 壓力
3. 聯接鍵**資料型別一致、高選擇性** 時 Hash Join 效果最好
4. 避免在聯接鍵上使用函數/運算 (導致無法使用 Hash/索引)

讀懂 Hash Join 代價模型與執行計畫，你就掌握了大資料量聯接優化的核心鑰匙。