Oracle Join 關聯機制深度解析：Hash Join、Nested Loops 與 Merge Join 效能優化指南

Oracle Join 關聯機制深度解析

在 Oracle 資料庫效能調校中，表連接的方式對 SQL 執行效率有決定性影響。優化器主要依據統計資訊、表大小、索引情況及連接條件，自動選擇最適合的 Join 方式。理解三種核心連接演算法——Hash Join、Nested Loops 與 Sort Merge Join 的運作原理、適用場景及優缺點，是編寫高效 SQL 與進行執行計劃分析的基石。

一、三大核心 Join 方式詳細比較

1. Hash Join（雜湊連接）

運作原理： 優化器選擇較小的表作為「建構輸入」，在記憶體中依據 Join Key 建立雜湊表；隨後掃描另一張較大的表（「探測輸入」），針對每一行計算雜湊值並在雜湊表中尋找匹配行。

• 最佳場景： 大表連接大表、大表連接小表（且小表能放入記憶體）、等值連接條件。
• 記憶體考量： 若 PGA_AGGREGATE_TARGET 足夠，小表完全駐留記憶體，成本約為兩表各一次全表掃描；記憶體不足時會溢寫至臨時表空間，導致 I/O 開銷劇增。
• 限制： 僅支援等值連接。

2. Nested Loops（巢狀迴圈連接）

運作原理： 以外層驅動表為基礎，逐行讀取外層表資料，對每一行依據連接條件至內層表進行存取（通常透過索引範圍掃描）。

• 最佳場景： 小表驅動大表、返回結果集較小、內層表有高效存取路徑（如唯一索引或高選擇性索引）、OLTP 系統高頻小量查詢。
• 驅動表選擇關鍵： 驅動表應為過濾後行數最少的表，以減少內層表存取次數。
• 限制： 若外層表行數大、內層表無索引或連接條件非等值，效能會急劇下降。

3. Sort Merge Join（排序合併連接）

運作原理： 分別對兩張表進行全表掃描並按 Join Key 排序，隨後合併兩個已排序的資料流。若資料本身已有序（如索引全掃描），可省略排序步驟。

• 最佳場景： 非等值連接、資料量極大且已排序、Hash Join 因記憶體不足大量溢寫磁碟時。
• 特點： 無驅動表概念，雙邊對等；排序操作可能消耗大量 TEMP 空間。

二、Join 次序優化原則

多表連接時，Join Order 直接決定中間結果集大小。核心原則：優先連接過濾後行數最少的表。

1. 選擇能過濾掉最多行的表作為驅動表。
2. 在剩餘表中，選擇過濾效果次好的表優先加入連接。
3. 以此類推，使中間結果集始終維持在最小規模。

範例：SELECT ... FROM taba a, tabb b, tabc c WHERE a.acol BETWEEN 100 AND 200 AND b.bcol BETWEEN 10000 AND 20000 AND c.ccol BETWEEN 10000 AND 20000 AND a.key1 = b.key1 AND a.key2 = c.key2;
若 a 表過濾後行數最少，應先連接 a 和 b，結果再連接 c。

三、使用 Hint 干預 Join 策略

1. 指定連接方式

• USE_NL(table)、USE_HASH(table)、USE_MERGE(table)
• 子查詢專用：NL_SJ、HASH_SJ、MERGE_SJ (EXISTS)；NL_AJ、HASH_AJ、MERGE_AJ (NOT IN)

2. 指定連接次序

• ORDERED：依 FROM 子句出現順序連接。
• LEADING(table1 table2 ...)：更靈活，明確指定驅動表順序（推薦使用）。

3. 進階：SWAP_JOIN_INPUTS 調整 Hash Join 建構/探測角色

在 LEADING(a b c) 指定順序下，若 a-b 連接結果集較大，希望後續與 c 連接時以 c 為建構端，可使用：
SELECT /*+ LEADING(a b c) SWAP_JOIN_INPUTS(c) */ ...
此 Hint 僅對 Hash Join 有效，能在不改變邏輯次序下優化記憶體使用。

四、Outer Join 特殊機制

• Nested Loop Outer Join： 強制以 Preserved Table 為驅動表，而非基於成本選擇。
• Full Outer Join (11g+)： 預設採用 Hash Join 算法，執行計劃顯示 HASH JOIN FULL OUTER。可用 NATIVE_FULL_OUTER_JOIN / NO_NATIVE_FULL_OUTER_JOIN 控制。

五、實戰建議：WHERE 條件 vs. JOIN ON

兩種語法經優化器轉換後執行計劃通常一致，但強烈建議使用 JOIN ... ON 標準語法：
✅ 可讀性更強，連接條件與過濾條件分離
✅ 避免遺漏連接條件導致笛卡兒積
✅ 符合 ANSI SQL 標準，利於移植與維護

六、PL/SQL Developer 64 位環境連接 Oracle 11g 配置要點

1. 下載 32 位 Oracle Instant Client（即使 OS 為 64 位，PL/SQL Dev 為 32 位進程需 32 位 OCI）。
2. 解壓至目錄（如 C:\instantclient_11_2），複製 tnsnames.ora 至該目錄。
3. PL/SQL Dev 中設定：
   Tools → Preferences → Connection →
   Oracle Home: C:\instantclient_11_2
   OCI Library: C:\instantclient_11_2\oci.dll
4. 建立啟動批次檔設定環境變數：

@echo off
set PATH=C:\instantclient_11_2;%PATH%
set ORACLE_HOME=C:\instantclient_11_2
set TNS_ADMIN=C:\instantclient_11_2
set NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
start plsqldev.exe