Oracle SQL 執行效率提升三大核心技巧：ORDERED、ORDERED_PREDICATES、OPTIMIZER_SEARCH_LIMIT

背景

復雜多表連接 (7+ 表) 的解析階段可能耗費 30+ 分鐘 (8 表 40,000+ 種連接順序)。以下三種手段可大幅縮減解析時間與優化器搜索空間。

技巧一：ORDERED Hint — 固定連接順序

SELECT /*+ ORDERED USE_NL(b) PARALLEL(e,4) */
       e.ename, e.hiredate, b.comm
FROM   emp e, bonus b
WHERE  e.ename = b.ename;

• 作用： 強制按 FROM 子句順序連接，**跳過連接順序枚舉**。
• 驅動表選擇： FROM 中第一張表為驅動表，應選**返回行數最少**的表 (經過 WHERE 過濾後)。
• 配合： 常與 USE_NL、USE_HASH、PARALLEL 組合使用。

技巧二：ORDERED_PREDICATES Hint — 固定 WHERE 條件評估順序

默認評估順序：

1. 子查詢優先
2. 無函數條件按 WHERE 逆序 (最後寫的最先評估)
3. 有函數條件按預估 CPU 成本升序

使用：

SELECT /*+ ORDERED_PREDICATES */ *
FROM   big_table
WHERE  expensive_plsql_func(col1) = 'Y'  -- 高成本函數
  AND  col2 = 100;                        -- 低成本列比較

• 強制按 WHERE 書寫順序評估，將高選擇性、低成本條件前置，提前過濾減少函數調用次數。
• 限制： 不影響索引鍵訪問路徑的選擇，僅影響殘餘條件評估順序。

技巧三：OPTIMIZER_SEARCH_LIMIT 參數 — 限制連接順序枚舉上限

ALTER SESSION SET optimizer_search_limit = 5;  -- 默認 5
-- 或實例級
ALTER SYSTEM SET optimizer_search_limit = 5 SCOPE=SPFILE;

• 控制優化器評估的最大連接順序組合數。
• 表數 ≤ 該值時啟用 Star Transformation Hint；表數 > 該值時優化器採用啟發式/贪婪算法而非窮舉。
• 防止優化器在「尋找最佳順序」上花費超過「執行最佳順序」的時間。

綜合實戰模板

SELECT /*+ 
    ORDERED 
    ORDERED_PREDICATES 
    USE_HASH(t2 t3) 
    USE_NL(t4) 
    PARALLEL(t1, 8) 
*/
       t1.id, t2.name, t3.amount, t4.status
FROM   fact_table t1
JOIN   dim_small t2 ON t1.dim2_id = t2.id
JOIN   dim_medium t3 ON t1.dim3_id = t3.id
JOIN   dim_large t4 ON t1.dim4_id = t4.id
WHERE  t1.dt = TRUNC(SYSDATE)
  AND  t2.is_active = 'Y'
  AND  expensive_check(t3.code) = 1
  AND  t4.region = 'APAC';

說明：
- fact_table 為驅動表 (日期分區裁剪後行數最少)
- ORDERED_PREDICATES 確保 t2.is_active 先於 expensive_check 評估
- 連接方式按維表大小選擇 HASH/NL

適用邊界與風險

• ORDERED：統計信息嚴重偏差時可能鎖定劣質計劃，建議配合 OPT_ESTIMATE 或 CARDINALITY Hint 修正基數。
• ORDERED_PREDICATES：僅影響 Filter 操作，不改變 Access Path。
• optimizer_search_limit：設置過小可能導致優化器錯過更優計劃，建議 5-8 之間。