Oracle 優化器 (CBO) 在大多數情況下能自動產生最佳執行計畫，但在統計資訊不足、資料分佈傾斜、複雜連接等場景，DBA/開發人員需透過 Hint (提示) 強制指定存取路徑、連接順序或優化目標。本文整理 30 個實務常見 Hint，附語法、用途與範例，供快速查閱。
| Hint | 用途 | 範例 |
|---|---|---|
/*+ ALL_ROWS */ | 以「最小化總資源消耗」為目標，適合批次/報表查詢 | SELECT /*+ ALL_ROWS */ emp_no FROM bempms WHERE emp_no='SCOTT'; |
/*+ FIRST_ROWS */ | 以「最快回應第一筆資料」為目標，適合互動式畫面 | SELECT /*+ FIRST_ROWS */ emp_no FROM bempms WHERE emp_no='SCOTT'; |
/*+ CHOOSE */ | 有統計資訊用 CBO，無則用 RBO (已棄用，僅相容舊版) | SELECT /*+ CHOOSE */ emp_no FROM bempms WHERE emp_no='SCOTT'; |
/*+ RULE */ | 強制使用規則基礎優化器 (RBO)，Oracle 10g+ 已不建議使用 | SELECT /*+ RULE */ emp_no FROM bempms WHERE emp_no='SCOTT'; |
| Hint | 用途 | 範例 |
|---|---|---|
/*+ FULL(table) */ | 全表掃描 | SELECT /*+ FULL(a) */ emp_no FROM bempms a WHERE emp_no='SCOTT'; |
/*+ ROWID(table) */ | 指定以 ROWID 存取 | SELECT /*+ ROWID(bempms) */ * FROM bempms WHERE rowid>='AAAAAAAAAAAAAA'; |
/*+ CLUSTER(table) */ | 叢集掃描 (僅限叢集表) | SELECT /*+ CLUSTER */ bempms.emp_no FROM bempms, bsdptms WHERE bempms.dpt_no=bsdptms.dpt_no; |
/*+ INDEX(table index) */ | 指定索引掃描 | SELECT /*+ INDEX(bempms sex_index) */ * FROM bempms WHERE sex='M'; |
/*+ INDEX_ASC(table index) */ | 索引遞增掃描 | SELECT /*+ INDEX_ASC(bempms pk_bempms) */ * FROM bempms WHERE dpt_no='SCOTT'; |
/*+ INDEX_DESC(table index) */ | 索引遞減掃描 | SELECT /*+ INDEX_DESC(bempms pk_bempms) */ * FROM bempms WHERE dpt_no='SCOTT'; |
/*+ INDEX_COMBINE(table index1, index2) */ | 位圖索引布林組合 | SELECT /*+ INDEX_COMBINE(bempms sal_bmi hiredate_bmi) */ * FROM bempms WHERE sal<5000000 AND hiredate |
/*+ INDEX_JOIN(table index1, index2) */ | 索引連接存取 (避免回表) | SELECT /*+ INDEX_JOIN(bempms sal_hmi hiredate_bmi) */ sal, hiredate FROM bempms WHERE sal<60000; |
/*+ INDEX_FFS(table index) */ | 快速全索引掃描 (替代全表掃描) | SELECT /*+ INDEX_FFS(bempms in_empnam) */ * FROM bempms WHERE dpt_no='TEC305'; |
/*+ ADD_EQUAL(table index1, index2) */ | 合併多個單欄索引掃描 | SELECT /*+ INDEX_FFS(bempms in_dptno, in_empno, in_sex) */ * FROM bempms WHERE emp_no='SCOTT' AND dpt_no='TDC306'; |
| Hint | 用途 | 範例 |
|---|---|---|
/*+ ORDERED */ | 依 FROM 子句順序連接 | SELECT /*+ ORDERED */ a.col1,b.col2 FROM table1 a,table2 b WHERE a.col1=b.col1; |
/*+ LEADING(table) */ | 指定驅動表 (建議搭配 ORDERED) | SELECT /*+ LEADING(a) */ * FROM table1 a, table2 b WHERE a.id=b.id; |
/*+ USE_NL(table) */ | 巢狀迴圈連接 (指定內部表) | SELECT /*+ ORDERED USE_NL(bempms) */ * FROM bsdptms, bempms WHERE bsdptms.dpt_no=bempms.dpt_no; |
/*+ USE_MERGE(table) */ | 排序合併連接 | SELECT /*+ USE_MERGE(bempms,bsdptms) */ * FROM bempms,bsdptms WHERE bempms.dpt_no=bsdptms.dpt_no; |
/*+ USE_HASH(table) */ | 雜湊連接 | SELECT /*+ USE_HASH(bempms,bsdptms) */ * FROM bempms,bsdptms WHERE bempms.dpt_no=bsdptms.dpt_no; |
/*+ DRIVING_SITE(table) */ | 強制在指定站點執行分散式查詢 | SELECT /*+ DRIVING_SITE(dept) */ * FROM bempms, dept@bsdptms WHERE bempms.dpt_no=dept.dpt_no; |
| Hint | 用途 | 範例 |
|---|---|---|
/*+ USE_CONCAT */ | 將 WHERE 中的 OR 條件轉為 UNION ALL | SELECT /*+ USE_CONCAT */ * FROM bempms WHERE dpt_no='TDC506' AND sex='M'; |
/*+ NO_EXPAND */ | 阻止 OR/IN-LIST 擴展為 UNION ALL | SELECT /*+ NO_EXPAND */ * FROM bempms WHERE dpt_no='TDC506' AND sex='M'; |
/*+ REWRITE */ | 啟用物化視圖重寫 (可指定視圖名) | SELECT /*+ REWRITE(mv_sales) */ * FROM sales WHERE year=2023; |
/*+ NO_REWRITE */ | 禁止查詢重寫 | SELECT /*+ NO_REWRITE */ * FROM sales WHERE year=2023; |
/*+ MERGE(view) */ | 合併視圖查詢區塊 | SELECT /*+ MERGE(v) */ a.emp_no FROM bempms a, (SELECT dpt_no,AVG(sal) avg_sal FROM bempms GROUP BY dpt_no) v WHERE a.dpt_no=v.dpt_no AND a.sal>v.avg_sal; |
/*+ NO_MERGE(view) */ | 禁止視圖合併 | SELECT /*+ NO_MERGE(v) */ a.emp_no FROM bempms a, (SELECT dpt_no,AVG(sal) avg_sal FROM bempms GROUP BY dpt_no) v WHERE a.dpt_no=v.dpt_no AND a.sal>v.avg_sal; |
| Hint | 用途 | 範例 |
|---|---|---|
/*+ CACHE(table) */ | 全表掃描時將區塊置於 LRU 熱端 | SELECT /*+ FULL(bempms) CACHE(bempms) */ emp_nam FROM bempms; |
/*+ NOCACHE(table) */ | 全表掃描時將區塊置於 LRU 冷端 (預設) | SELECT /*+ FULL(bempms) NOCACHE(bempms) */ emp_nam FROM bempms; |
/*+ APPEND */ | 直接路徑插入 (High Water Mark 之後)，加速大量載入 | INSERT /*+ APPEND */ INTO test1 SELECT * FROM test4; |
/*+ NOAPPEND */ | 停用並行模式，執行傳統插入 | INSERT /*+ NOAPPEND */ INTO test1 SELECT * FROM test4; |
EXPLAIN PLAN 驗證實際執行計畫。DBMS_STATS.GATHER_TABLE_STATS)、建立適當索引、重寫 SQL，再考慮 Hint。/*+ OPT_PARAM('_optimizer_adaptive_plans' 'false') */ 等內部參數 Hint，需極度謹慎使用。熟練掌握上述 Hint，能在優化器「選錯路」時快速干預。但請記住：Hint 是最後手段，而非首選方案。持續優化統計資訊、索引設計與 SQL 寫法，才是穩定效能的根本之道。
