Oracle SQL 執行效率優化三大實戰技巧：ORDERED、ORDERED_PREDICATES、OPTIMIZER_SEARCH_LIMIT

技巧一：ORDERED Hint — 指定連接順序，跳過排列組合解析

多表連接時，Oracle 需評估所有連接順序排列組合 (8 表 = 40,320 種)。ORDERED 強制依 FROM 子句順序連接，首張表為驅動表 (應選擇回傳行數最少的表)。

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

• 搭配 USE_NL、PARALLEL 等 Hint 形成完整執行計畫控制。
• 驅動表選擇原則：過濾後行數最少 的表放在 FROM 最前面。

技巧二：ORDERED_PREDICATES Hint — 控制 WHERE 條件評估順序

預設評估順序：

1. 子查詢優先。
2. 無函數/子查詢條件，按 WHERE 子句 逆序 (最後寫的最先評估)。
3. 含函數條件，按預估成本遞增。

ORDERED_PREDICATES 強制依 WHERE 子句 撰寫順序 評估，適用：

• WHERE 子句包含 PL/SQL 函數，需控制呼叫次序。
• 明知某條件過濾力最強 (如唯一鍵 = 值)，希望最先評估縮減結果集。

SELECT /*+ ORDERED_PREDICATES */
       *
FROM   big_table
WHERE  expensive_plsql_func(col) = 'Y'  -- 故意放最後，避免大量呼叫
AND    status = 'ACTIVE'                -- 高選擇性條件放前面
AND    created_date > SYSDATE - 30;

技巧三：OPTIMIZER_SEARCH_LIMIT 參數 — 限制連接順序評估上限

• 控制基於成本優化器 (CBO) 評估連接順序組合的最大數量。
• 預設值 5 (表數 ≤ 5 才全評估，超過則啟發式搜尋)。
• 調大可讓優化器更充分搜尋，但解析時間指數增長；調小 (如 3) 可加快解析，風險是錯過最佳計畫。
• 同時控制 STAR_TRANSFORMATION Hint 觸發閾值 (表數 < 該值才啟用)。

ALTER SESSION SET OPTIMIZER_SEARCH_LIMIT = 10;  -- 允許最多評估 10! = 3.6M 種順序 (慎用)
-- 或系統級
ALTER SYSTEM SET OPTIMIZER_SEARCH_LIMIT = 5 SCOPE=BOTH;

綜合建議

1. 優先收集完整統計資訊，讓 CBO 自動找到最佳計畫。
2. 必要時用 ORDERED 固定連接順序，搭配驅動表原則。
3. 複雜 WHERE 條件含 PL/SQL 函數時加 ORDERED_PREDICATES。
4. OPTIMIZER_SEARCH_LIMIT 通常保持預設，極少調整。
5. 長期優化應著眼於索引設計、分區、SQL 改寫，而非依賴 Hint。