Oracle 表連接機制深度解析：Sort Merge Join 原理、特性、優化與實戰調優

Oracle 表連接機制深度解析：Sort Merge Join (SMJ)

一、核心原理

Sort Merge Join (排序合併連接) 適用於**非等值連接** (>, <, >=, <=, !=) 或**已排序資料集**場景：

1. 驅動表：依謂詞過濾 → 依連接鍵 **排序**
2. 被驅動表：依謂詞過濾 → 依連接鍵 **排序**
3. 合併階段**：雙指標掃描兩有序集合，匹配連接條件輸出結果

適用條件：Hash Join 僅支援等值連接 (`=`)，非等值連接**只能**用 SMJ 或 Nested Loops。若連接鍵**已有索引/預排序**，SMJ 效能優於 NL。

二、關鍵特性

三、實測驗證 (t3 1,000 行 / t4 10,000 行)

1. 等值連接 + 無過濾 → SMJ (非最優，Hash Join 更好)

SELECT /*+ LEADING(t3) USE_MERGE(t4) */ * FROM t3, t4 WHERE t3.id = t4.t3_id;
-- Plan: MERGE JOIN → SORT JOIN (t3) → SORT JOIN (t4)
-- Buffers: 119

2. 驅動表互換 (t4 驅動 t3) → 成本幾乎相同

SELECT /*+ LEADING(t4) USE_MERGE(t3) */ * FROM t3, t4 WHERE t3.id = t4.t3_id;
-- Buffers: 119 (驅動表選擇影響極小)

3. 加入謂詞過濾 (t3.n=1100, t4.n=10034) → 全表掃描依然

SELECT /*+ LEADING(t3) USE_MERGE(t4) */ * FROM t3, t4
WHERE t3.id = t4.t3_id AND t3.n = 1100 AND t4.n = 10034;
-- Buffers: 119 (無索引，全掃描後排序)

4. 建立索引 (t4.n, t3.n) → 索引範圍掃描大幅降低 Buffers

CREATE INDEX t4_n ON t4(n);
-- Buffers: 18 (INDEX RANGE SCAN + TABLE ACCESS BY INDEX ROWID)

CREATE INDEX t3_n ON t3(n);
-- Buffers: 6 (雙索引範圍掃描)

四、執行計劃關鍵指標解讀

五、SMJ 優化三板斧

1. 建立連接鍵/謂詞欄位索引**：避免全表掃描 + 大量排序，轉為 Index Range Scan → 減少排序輸入行數。
2. 擴大 PGA (PGA_AGGREGATE_TARGET / MEMORY_TARGET)**：確保排序在記憶體完成，**Used-Mem 後括號為 0**。
3. 精簡 SELECT 列表**：排序行寬越窄，記憶體佔用越少，磁盤排序概率越低。

六、調優決策樹

						
						
 
						
 
	
						

						
						

							
上一篇文章： Parallel Query 導致 ORA-04031：PX Msg Pool 從 Shared Pool 分配耗盡，Large Pool 未生效根因分析 
下一篇文章： PL/SQL 基礎系列 8：常用字符、數學、轉換與日期函數詳解 
						
					
				
				

					

						Oracle 教學
						

					
					

						
						

							
							Oracle ORA-01652 臨時表空間不足實戰：並行載入觸發排序溢寫與分級解決方案
							
Oracle ORA-01652 臨時表空間不足實戰：並行載			
						
						
						

							
							Oracle 日期計算：依 ISO 週規則將前幾天歸屬上個月的 SQL 實作
							
業務規則
若某月第一週的天數 ≤ 3 天，則該週歸屬上一個月			
						
						
						

							
							PL/SQL Developer 導入導出 Oracle 資料庫完整操作指南
							
工具簡介
PL/SQL Developer 是 Oracle			
						
						
						

							
							SQL 進階查詢練習：子查詢、集合運算、相關子查詢與分析函數
							
前言
本文延續基礎查詢練習，進階涵蓋多層子查詢、UNION/			
						
						
						

					
				
			
			

				
				
 
	
				

					

						

					
					

						
						Oracle 11g 自動診斷儲存庫 (ADR) 架構與管理實務
						
						Oracle 適配器連線失敗：修改 listener.ora 註冊 SID 解決 ORA-12505
						
						Oracle PL/SQL基礎入門：架構、語法、數據類型與控制結構全解析
						
						Linux 下 Oracle 資料庫安裝、啟動關閉操作完整教學
						
						Oracle資料庫查詢優化與子查詢優化實戰指南
						
						Oracle 行列轉換 (PIVOT/UNPIVOT) 實戰：DECODE/CASE 與 11g PIVOT 語法對比
						
						Oracle LogMiner 深度實戰：Redo/Archive Log 挖掘恢復誤操作數據全流程
						
					
				
			
				

					
欄目導航
					

						
Oracle 資料庫相關知識
Oracle 資料庫基礎
Oracle 教學
					
					
				
			
			

		
	
		

	
Copyright © 程式碼之家 All Rights Reserved