前言
Oracle 10g 大幅擴充優化器 Hints，提供更精細的執行計畫控制能力。本文系統介紹 10g 新增的關鍵 Hints：電子表格分析控制、巢狀迴圈連接強制索引、基數/選擇性指定、連接方法禁用、快速全索引掃描禁用，並附帶語法範例與適用場景分析。
一、電子表格優化控制 Hints (SQL Modeling)
1. SPREAD_MIN_ANALYSIS
SELECT /*+ SPREAD_MIN_ANALYSIS */ ...
- 忽略詳細關聯依賴圖分析等編譯期優化規則
- 保留：過濾器建立、選擇性存取結構限制、修訂規則
- 適用：規則極多 (數千+) 的 SQL Model 查詢，編譯時間從數小時降至可接受範圍
2. SPREAD_NO_ANALYSIS
SELECT /*+ SPREAD_NO_ANALYSIS */ ...
- 完全停用電子表格分析
- 同時忽略修訂規則與過濾器產生
- 編譯時間降至最低
- 風險：可能產生非最佳執行計畫，僅建議極度編譯超時場景使用
二、連接方法控制 Hints
3. USE_NL_WITH_INDEX
SELECT /*+ USE_NL_WITH_INDEX(t1 t1_idx) */ ...
FROM t1, t2 WHERE t1.id = t2.id;
- 強制 CBO 使用巢狀迴圈 (Nested Loop) 連接
- 指定內部表 (t1) 必須使用特定索引 (t1_idx)
- 條件：
- 未指定索引時：CBO 必須能使用某索引，且該索引至少一欄位參與等值連接
- 指定索引時：該索引至少一欄位參與等值連接
- 適用：小結果集驅動大表索引查詢的經典 NLJ 場景
4. NO_USE_NL
SELECT /*+ NO_USE_NL(employees) */ ...
- 禁止指定表作為內部表的巢狀迴圈連接
- 僅允許 Hash Join、Sort-Merge Join
- 適用：統計資訊導致錯誤選擇 NLJ 造成大量隨機 I/O 時
5. NO_USE_MERGE
SELECT /*+ NO_USE_MERGE(employees dept) */ ...
- 禁止指定表的排序合併連接
- 適用：排序成本過高、記憶體不足導致磁碟排序時
6. NO_USE_HASH
SELECT /*+ NO_USE_HASH(employees dept) */ ...
- 禁止指定表的雜湊連接
- 適用：雜湊表過大溢寫磁碟、建立雜湊表成本過高時
三、基數與選擇性指定 Hints
7. CARDINALITY
SELECT /*+ CARDINALITY([tablespec] card) */ ...
- 指定查詢或子查詢預估回傳行數
tablespec 可省略，表示整個查詢總行數- 適用：CBO 統計資訊缺失/過期導致基數嚴重偏差時
8. SELECTIVITY
SELECT /*+ SELECTIVITY([tablespec] sel) */ ...
- 指定查詢或子查詢選擇性 (0~1)
- 單表：滿足單表條件的行比例
- 多表：所有條件合併後結果行比例
- 注意：同一組表同時指定 CARDINALITY 與 SELECTIVITY 會被雙雙忽略
9. NO_INDEX_FFS
SELECT /*+ NO_INDEX_FFS(t1 idx_t1) */ ...
- 禁止指定表的指定索引進行快速全索引掃描
- 適用：FFS 成本被低估、實際多區塊讀取效能不佳時
四、Hints 使用最佳實務
- 最後手段：統計資訊收集、SQL 改寫、索引設計優先於 Hints
- 最小干預：僅針對具體問題表/連接/操作加 Hint，避免全域硬編碼
- 版本相容：升級前測試所有 Hint 效果，部分 Hint 可能棄用或語義變更
- 文檔化：註解說明加 Hint 理由、預期效果、驗證結果
- 監控失效：結構變更 (索引增刪、分區調整) 可能導致 Hint 無效或報錯
五、10g 其他重要優化增強 (補充)
- SQL Profile / SQL Tuning Advisor：自動調優替代手工 Hint
- DBMS_STATS 增強：系統統計、直方圖、延遲發布統計
- 動態取樣：
OPTIMIZER_DYNAMIC_SAMPLING 級別控制
- 成本模型改進：CPU 成本、I/O 校準、多區塊讀取計數自動調整
結語
Oracle 10g Hints 新特性為資深 DBA 提供了手術刀級別的執行計畫控制工具。從電子表格編譯優化到連接方法精確禁用，再到基數/選擇性外部指定，每一個 Hint 都針對特定痛點設計。但權力越大責任越大：濫用 Hints 會讓 SQL 變得脆弱、難維護、升級風險高。建議建立「Hint 使用審核機制」，僅在統計資訊無法解決、SQL 改寫受限、效能影響業務的情況下，經審批後謹慎使用，並納入變更管理追蹤。
