前言：Hint 的雙刃劍
Oracle Hint (`/*+ ... */`) 是指導優化器生成特定執行計畫的指令。慎用原則：統計資訊完整、優化器模式正確時，優化器通常比人工更聰明。Hint 適用於：統計資訊缺失、複雜查詢優化器迷路、測試不同計畫、緊急繞過 Bug。
優化目標導向 Hint
| Hint | 目標 | 適用場景 |
|---|
/*+ ALL_ROWS */ | 最佳吞吐量 (總成本最低) | 批次處理、報表、資料倉儲 |
/*+ FIRST_ROWS(n) */ | 最佳響應時間 (前 n 行最快) | 互動式應用、分頁查詢、Web 頁面 |
/*+ CHOOSE */ | 有統計用 CBO，無統計用 RBO | 遷移期混合環境 (舊版本) |
/*+ RULE */ | 強制規則基礎優化 (RBO) | 極舊系統相容，現代版本已棄用 |
存取路徑控制 (單表)
| Hint | 行為 | 範例 |
|---|
/*+ FULL(t) */ | 全表掃描 | `SELECT /*+ FULL(emp) */ * FROM emp` |
/*+ ROWID(t) */ | 指定 ROWID 存取 | `SELECT /*+ ROWID(emp) */ * FROM emp WHERE rowid > '...'` |
/*+ CLUSTER(t) */ | 叢集掃描 (僅叢集表) | `SELECT /*+ CLUSTER */ * FROM emp, dept WHERE ...` |
/*+ INDEX(t idx) */ | 指定索引範圍掃描 | `SELECT /*+ INDEX(emp emp_sex_idx) */ * FROM emp WHERE sex='M'` |
/*+ INDEX_ASC(t idx) */ | 索引遞增掃描 | `SELECT /*+ INDEX_ASC(emp pk_emp) */ * FROM emp WHERE deptno=10` |
| /*+ INDEX_DESC(t idx) */ | 索引遞減掃描 | `SELECT /*+ INDEX_DESC(emp pk_emp) */ * FROM emp WHERE deptno=10` |
/*+ INDEX_FFS(t idx) */ | 快速全索引掃描 (代替全表掃描) | `SELECT /*+ INDEX_FFS(emp emp_name_idx) */ * FROM emp WHERE deptno=20` |
/*+ INDEX_COMBINE(t idx1 idx2) */ | 位圖索引合併 (AND/OR) | `SELECT /*+ INDEX_COMBINE(emp sal_bmi hiredate_bmi) */ * FROM emp WHERE sal<5000 AND hiredate |
/*+ INDEX_JOIN(t idx1 idx2) */ | 索引連接 (不回表) | `SELECT /*+ INDEX_JOIN(emp sal_idx hd_idx) */ sal, hiredate FROM emp WHERE sal<60000` |
/*+ AND_EQUAL(t idx1 idx2) */ | 合併多個單欄索引 (舊語法) | `SELECT /*+ AND_EQUAL(emp idx_dpt idx_emp idx_sex) */ * FROM emp WHERE empno=... AND deptno=...` |
查詢轉換與重寫控制
| Hint | 行為 | 說明 |
|---|
/*+ USE_CONCAT */ | 將 OR 條件轉為 UNION ALL | 避免全表掃描，各分支可用各自索引 |
/*+ NO_EXPAND */ | 阻止 OR/IN-LIST 展開 | 防止計畫膨脹 |
/*+ REWRITE(v) */ | 啟用物化視圖重寫 | 可指定視圖名 |
/*+ NO_REWRITE */ | 禁止查詢重寫 | 排查重寫錯誤 |
/*+ MERGE(v) */ | 強制視圖合併 (View Merging) | 將視圖定義合併至主查詢，開啟更多優化機會 |
/*+ NO_MERGE(v) */ | 禁止視圖合併 | 保護視圖邊界，或避免錯誤合併 |
多表連接策略控制
| Hint | 連接方式 | 適用條件 |
|---|
/*+ ORDERED */ | 依 FROM 子句順序連接 | 開發者確定最佳驅動順序時 |
/*+ LEADING(t) */ | 指定驅動表 (建議替代 ORDERED) | 更靈活，只固定第一張表 |
/*+ USE_NL(t) */ | Nested Loops (指定表為內部表) | 驅動集小、內部表有高選擇性索引 |
/*+ USE_MERGE(t) */ | Sort Merge Join | 雙邊大集合、已排序或無合適索引 |
/*+ USE_HASH(t) */ | Hash Join | 大表連接大表、等值連接、記憶體充足 |
/*+ DRIVING_SITE(t) */ | 強制在指定遠端站點執行 | 分散式查詢，減少網路傳輸 |
並行與快取控制
| Hint | 行為 | 備註 |
|---|
/*+ CACHE(t) */ | 全表掃描時將區塊置於 LRU 熱端 | 小熱表，避免被淘汰 |
/*+ NOCACHE(t) */ | 全表掃描區塊置於 LRU 冷端 (預設) | 大表掃描，保護熱資料 |
/*+ APPEND */ | 直接路徑插入 (High Water Mark 以上) | 大量載入，繞過 Undo/Redo 部分邏輯，速度極快 |
/*+ NOAPPEND */ | 傳統插入 (關閉並行模式) | 需觸發 Trigger/Constraint 時 |
/*+ PARALLEL(t, 4) */ | 指定並行度 (常用但非列表內) | 大表全掃/建索引/大量 DML |
實戰組合範例
-- 場景：大表連接，指定驅動表、Hash Join、並行 4，目標吞吐量
SELECT /*+ ALL_ROWS LEADING(orders) USE_HASH(customers) PARALLEL(orders, 4) PARALLEL(customers, 4) */
o.order_id, c.cust_name, o.order_total
FROM orders o, customers c
WHERE o.cust_id = c.cust_id
AND o.order_date > SYSDATE - 30;
-- 場景：分頁查詢，優化首頁響應，強制索引範圍掃描
SELECT /*+ FIRST_ROWS(20) INDEX(orders ord_dt_idx) */
order_id, order_date
FROM orders
WHERE order_date > SYSDATE - 7
ORDER BY order_date DESC
FETCH FIRST 20 ROWS ONLY; -- 12c+ 語法
避坑指南
- 綁定變量窺探：Hint 固定計畫可能導致參數嗅探問題，考慮 `OPT_PARAM('_optim_peek_user_binds', 'false')` 或 `ACS` (適應性游標共享)。
- 統計資訊為本：90% 的效能問題源於統計資訊過時/缺失。優先 `DBMS_STATS.GATHER_TABLE_STATS`，再考慮 Hint。
- 版本演進：11g+ 引入 SQL Plan Management (SPM)，建議用 SQL Plan Baseline 固定計畫，比 Hint 更安全、可演進。
- 註解語法：Hint 必須緊跟 `SELECT/INSERT/UPDATE/DELETE` 關鍵字後，格式 `/*+ HINT */`，大小寫不敏感。
