您现在的位置：程式碼之家 >> 數據庫知識 > >> Oracle 資料庫 >> Oracle 資料庫相關知識

Oracle 三大表連接算法全解析：Nested Loop、Sort Merge、Hash Join 原理、適用場景與調優指南

編輯：Oracle 資料庫相關知識

Oracle 優化器擁有三種核心表連接算法：Nested Loop Join（嵌套循環）、Sort Merge Join（排序合併）、Hash Join（哈希連接）。理解其底層運作機制、內存模型與適用邊界，是編寫高性能 SQL、解讀執行計劃、實施精準調優的基石。本文深度剖析三大算法原理與實戰選型邏輯。

一、算法原理與運作機制深度解析

1. Nested Loop Join (NLJ) —— 像讀書一樣「逐行查找」

核心邏輯：將數據集分為 Outer 驅動表（外層循環，執行 1 次）與 Inner 被驅動表（內層循環，執行次數 = Outer 返回行數）。對 Outer 每一行，在 Inner 中查找匹配行。

FOR outer_row IN (SELECT * FROM outer_table) LOOP
  FOR inner_row IN (SELECT * FROM inner_table WHERE join_key = outer_row.join_key) LOOP
    OUTPUT outer_row + inner_row;
  END LOOP;
END LOOP;

關鍵特徵：

內存佔用極小（僅需緩存 Outer 當前行 + Inner 索引/表塊）
高度依賴 Inner 表連接列索引：無索引 → Inner 全表掃描 → 性能災難
適合 小結果集驅動大表精準查找（OLTP 典型模式）

2. Sort Merge Join (SMJ) —— 「先排序，再合併」的雙向奔赴

核心邏輯：

分別對兩張表的連接鍵進行全量排序（Sort Phase），結果存入 PGA 工作區
雙指針掃描兩個有序結果集，匹配相同鍵值行（Merge Phase）

關鍵特徵：

排序開銷巨大：時間複雜度 O(N log N + M log M)，內存不足時落盤 Temp 表空間
不要求索引，適合無索引、大結果集、範圍連接（>、<、BETWEEN）
排序區參數：SORT_AREA_SIZE、SORT_AREA_RETAINED_SIZE（手動 PGA 管理模式）或 PGA_AGGREGATE_TARGET（自動模式）

3. Hash Join (HJ) —— 「以空間換時間」的內存哈希表

核心邏輯：

Build Phase：讀取較小表（Build Input），在 PGA 構建內存哈希表（Key = 連接鍵，Value = 所需列）
Probe Phase：掃描較大表（Probe Input），對每行計算哈希值探測哈希表，輸出匹配行

關鍵特徵：

僅支援等值連接（=），不支援範圍連接
內存佔用大：哈希表駐留 PGA，受 HASH_AREA_SIZE 或 PGA_AGGREGATE_TARGET 控制
內存不足時分區溢寫 Temp，性能急劇下降
適合大表關聯、無索引、等值連接、結果集較大（DW/OLAP 典型模式）

二、三大算法核心對比矩陣

維度	Nested Loop Join	Sort Merge Join	Hash Join
連接條件類型	等值、非等值均可	等值、範圍（>、<、>=、<=）	僅等值 (=)
驅動表大小要求	Outer 必須小（< 1萬行最佳）	無嚴格要求，但雙方排序開銷大	Build Input 必須小（能放入 PGA）
索引依賴度	極高（Inner 必須有索引）	無需索引	無需索引
內存消耗	極低（KB 級）	高（排序區，可能落盤）	高（哈希表，可能分區溢寫）
並行度支援	差（難以並行化）	好（排序可並行）	優（Build/Probe 均可並行）
典型應用場景	OLTP：主鍵/唯一索引查找、小表驅動大表	無索引大表關聯、範圍連接、並行查詢	DW/OLAP：大表等值關聯、星型查詢、事實表關聯維度表

三、優化器選擇邏輯與干預手段

1. 成本模型核心考量

NLJ 成本 ≈ Outer 行數 × (Inner 索引訪問成本 + 表訪問成本)
SMJ 成本 ≈ 兩表排序成本 + 合併掃描成本
HJ 成本 ≈ Build 表掃描 + 哈希表構建 + Probe 表掃描 + 探測成本

2. 常見誤選場景與修正

誤選現象	根因	修正策略
大表驅動小表走 NLJ	統計信息缺失/過期，優化器誤判基數	`DBMS_STATS.GATHER_TABLE_STATS`、`CARDINALITY` Hint
小表關聯大表走 HJ 但 PGA 不足溢寫 Temp	`PGA_AGGREGATE_TARGET` 設置過小	調大 PGA、或 Hint `USE_NL` 強制 NLJ
範圍連接被迫走 NLJ 性能差	無索引且優化器不考慮 SMJ	Hint `USE_MERGE`、創建函數索引
並行查詢強制走 NLJ	並行 Hint 與連接方法衝突	`PARALLEL` + `USE_HASH`/`USE_MERGE`

3. 關鍵 Hint 語法

-- 強制指定連接方法
SELECT /*+ USE_NL(b) */ * FROM a, b WHERE a.id = b.id;      -- Nested Loop
SELECT /*+ USE_HASH(b) */ * FROM a, b WHERE a.id = b.id;     -- Hash Join
SELECT /*+ USE_MERGE(b) */ * FROM a, b WHERE a.id = b.id;    -- Sort Merge

-- 指定驅動表順序
SELECT /*+ LEADING(a b) USE_HASH(b) */ * FROM a, b WHERE a.id = b.id;