Oracle 多表連接順序優化：優化器搜尋上限參數與 ORDERED/LEADING Hint 實戰

前言

多表連接 (Join) 時，優化器需在 N! 種連接順序中尋找最低 Cost 路徑。當表數超過 10，排列組合爆炸 (15 表 > 1 兆種)，解析時間可能超過執行時間。本文詳細說明控制優化器搜尋空間的參數與 Hint 實戰技巧。

連接順序排列組合爆炸

• 6 表 = 720 種、10 表 = 362 萬種、15 表 ≈ 1.3 兆種。
• 優化器使用 **動態規劃** 避免窮舉，但仍受參數限制。

控制參數

手工指定連接順序：ORDERED & LEADING

ORDERED Hint

SELECT /*+ ORDERED USE_NL(dept) */ e.ename, d.dname
FROM   emp e, dept d
WHERE  e.deptno = d.deptno;

• 強制依 **FROM 子句順序** 連接，第一張表為驅動表 (Outer)。
• 常搭配 USE_NL, USE_HASH, LEADING 微調。

LEADING Hint (推薦，更靈活)

SELECT /*+ LEADING(e d) USE_HASH(e d) USE_NL(s b) */
       ...
FROM   emp e, dept d, sal s, bonus b
WHERE  ...;

• 指定**連接順序清單**，不綁定 FROM 子句順序。
• 可部分指定：LEADING(e d) 只固定前兩表。

實戰調優流程

1. 收集完整統計資訊 (含直方圖)。
2. Session 級暫時調大 OPTIMIZER_SEARCH_LIMIT 讓優化器窮舉找最佳順序。
3. 觀察執行計畫，提取最佳連接順序。
4. 將順序寫入 LEADING Hint，或建立 **SQL Plan Baseline** 固化。
5. 恢復參數預設值。

連接順序選擇原則

「先過濾最嚴格的表 (最小結果集) 驅動，後連接大表；驅動表走索引範圍掃描，被驅動表走索引唯一/範圍掃描。」

• 將 **WHERE 條件最強、回傳行數最少** 的表放在最前 (驅動表)。
• 被驅動表的 JOIN KEY **必須有索引** 才能高效 Nested Loops。
• 避免大表做驅動表 (除非有極佳索引過濾)。

監控與驗證

-- 查看實際連接順序
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('sql_id', NULL, 'ALL'));
-- 關鍵欄位：OPERATION (NESTED LOOPS / HASH JOIN), OBJECT_NAME, OPTIONS

1. 上一篇文章： 深入解析 Oracle 全域暫存表（Global Temporary Table）機制與應用
2. 下一篇文章： Oracle 參數模糊查詢技巧：SHOW PARAMETER 配合通配符快速定位

Oracle 資料庫基礎

Oracle ESCAPE 轉義字符完整指南：LIKE 查詢中通配符_% 的精確匹配

核心語法 WHERE column LIKE pattern

Windows 32位 Oracle 11g R2 Client 安裝圖文教程

下載 Oracle 11g R2 Client (Win32

Oracle RAC 配置支持 WebSphere Process Server 完整實施指南

前言 Oracle Real Application Clu

Oracle 關聯查詢性能優化實戰：索引消除全表掃描降低成本 99%

從 191 成本到 189：三表 JOIN 優化全過程解析