Oracle Cost-Based Optimizer (CBO) 雖然強大，但面對複雜查詢、統計資訊不準或特殊業務模式時，仍可能產生次優執行計畫。**Hint (提示)** 是開發人員與 DBA 干預優化器、強制指定存取路徑、連接順序、並行度等關鍵決策的「方向盤」。本文系統整理 Hint 分類、語法規則、高頻實戰 Hint 與避坑指南。
Hint 基礎語法與生效規則
/*+ HINT_NAME([@queryblock] [tablespec] [parameters]) */
SELECT /*+ FULL(t) */ * FROM employees t WHERE department_id = 10;
- 放在
SELECT/INSERT/UPDATE/DELETE/MERGE 關鍵字**緊接之後**，以 /*+ ... */ 註釋形式。
- 可指定
@queryblock (如 @sel$1) 精準作用於子查詢/視圖內部區塊。
- Hint 僅是建議，若語法錯誤、語意衝突或導致錯誤結果，CBO 會**忽略**並走預設計畫 (不會報錯)。
- 多個 Hint 以空格分隔：
/*+ FULL(t) LEADING(t) USE_NL(t) */
高頻實戰 Hint 分類速查表
1. 存取路徑類
| Hint | 用途 | 典型場景 |
|---|
FULL(table) | 強制全表掃描 | 小表、無索引、統計資訊誤導 CBO 走索引 |
INDEX(table idx_name) | 強制使用指定索引 | CBO 選錯索引、函數索引未被識別 |
INDEX_FFS(table idx_name) | 索引快速全掃描 | 只需索引欄位、避免回表 |
NO_INDEX(table idx_name) | 禁止使用指定索引 | 索引失效、統計資訊偏差導致錯誤選擇 |
2. 連接方法類
| Hint | 用途 | 適用條件 |
|---|
USE_NL(table) | 巢狀迴圈連接 (Nested Loop) | 驅動表小、被驅動表有高選擇性索引 |
USE_HASH(table) | 雜湊連接 (Hash Join) | 大表連大表、無合適索引、記憶體足夠 |
USE_MERGE(table) | 排序合併連接 (Sort Merge) | 大表連大表、已排序、非等值連接 |
LEADING(table1 table2 ...) | 指定連接順序 (驅動表優先) | 控制中間結果集大小、避免 Cartesian Product |
SWAP_JOIN_INPUTS(table) | 交換 Hash Join 的 Build/Probe 端 | 優化記憶體使用、適應並行執行 |
3. 並行與資源類
| Hint | 用途 | 注意事項 |
|---|
PARALLEL(table n) | 指定並行度 n | 需 Enterprise Edition、消耗 CPU/IO、小表勿用 |
NO_PARALLEL(table) | 禁止並行 | 防止非預期並行導致資源爭用 |
PQ_DISTRIBUTE(table outer_dist inner_dist) | 控制並行分發方式 | 進階調優、需配合執行計畫分析 |
4. 查詢轉換與優化策略類
| Hint | 用途 |
|---|
MERGE(view) / NO_MERGE(view) | 強制/禁止視圖/子查詢合併 |
PUSH_PRED(view) / NO_PUSH_PRED(view) | 強制/禁止謂詞下推 |
UNNEST(subquery) / NO_UNNEST(subquery) | 強制/禁止子查詢展開為連接 |
OPT_PARAM('_optim_peek_user_binds' 'false') | 會話級關閉 Bind Variable Peeking (11g+) |
CARDINALITY(table n) | 人工指定基數 (行數估算) |
SELECTIVITY(table sel) | 人工指定選擇率 (0~1) |
OPT_ESTIMATE(...) | 12c+ 更精細的基數/選擇率/成本調整 |
5. 特殊功能類
APPEND / APPEND_VALUES：直接路徑插入 (Direct Path Load)，繞過 Buffer Cache、高水位線直寫，大量載入首選。RESULT_CACHE：查詢結果快取 (11g+)，適合頻繁執行、資料變動極少的查詢。DRIVING_SITE(table)：分散式查詢指定驅動站點，減少網路傳輸。DYNAMIC_SAMPLING(table n)：動態採樣級別 (0-11)，統計資訊缺失時救急。
進階實戰：複雜查詢 Hint 組合範例
/*
場景：大筆訂單表 ORDERS (1億行) 連小維度表 CUSTOMERS (1萬行)
目標：強制 CUSTOMERS 為驅動表，走 NL Join + 索引，並行掃描 ORDERS 分區
*/
SELECT /*+
LEADING(c o)
USE_NL(o)
INDEX(o idx_orders_cust_id)
PARALLEL(o 8)
PQ_DISTRIBUTE(o NONE BROADCAST) -- 小表廣播給所有並行奴隸
*/
c.cust_name, o.order_id, o.order_date
FROM customers c, orders o
WHERE c.cust_id = o.cust_id
AND c.region = 'APAC'
AND o.order_date >= DATE '2024-01-01';
避坑指南與最佳實務
- 以統計資訊為本：Hint 是**最後手段**。優先收集完整、準確的統計資訊 (
DBMS_STATS.GATHER_TABLE_STATS)，解決 90% 以上計畫問題。
- 使用 SQL Plan Baseline (SPB) 替代硬編碼 Hint：11g+ 透過
DBMS_SPM 固化優良計畫，避免程式碼散落 Hint、升級失效風險。
- 查詢區塊命名 (QB_NAME)：複雜視圖/子查詢中，給關鍵區塊命名
/*+ QB_NAME(main) */，再用 @main 精準施策。
- 測試驗證：每次加 Hint 必須對比
EXPLAIN PLAN、DBMS_XPLAN.DISPLAY_CURSOR、實際執行時間 (多次執行取中位數)。
- 文檔化：註釋說明 **「為何加此 Hint、解決什麼問題、預期效果、風險點」**，便於後續維護評估是否移除。
常見無效原因排查
- 拼寫錯誤 (大小寫不敏感，但關鍵字必須正確)
- 表別名不匹配 (Hint 內需使用查詢中定義的別名)
- 語意衝突 (如同時指定
USE_NL 和 USE_HASH)
- 版本不支援 (查閱
V$SQL_HINT 確認當前版本支援清單)
- 被更高優先級機制覆蓋 (如 SQL Profile、SPB、OPT_PARAM)
Hint 是雙面刃：用得好是精準手術刀，用不好是技術債炸彈。**「統計資訊優先、SPB 固化次之、Hint 最後、必須文檔化」** —— 這是成熟團隊的鐵律。
