| Hint | 語法 | 含義 | 範例 |
|---|
| FULL | /*+ FULL(alias) */ | 全表掃描 | /*+ FULL(a) */ SELECT * FROM emp a |
| ROWID | /*+ ROWID(alias) */ | ROWID 訪問 | /*+ ROWID(emp) */ SELECT * FROM emp WHERE rowid > '...' |
| CLUSTER | /*+ CLUSTER(alias) */ | 簇掃描（僅簇表） | /*+ CLUSTER */ SELECT ... FROM emp, dept WHERE ... |
| INDEX | /*+ INDEX(alias index_name) */ | 指定索引掃描 | /*+ INDEX(emp sex_idx) */ SELECT * FROM emp WHERE sex='M' |
| INDEX_ASC | /*+ INDEX_ASC(alias idx) */ | 索引升序掃描 | /*+ INDEX_ASC(emp pk_emp) */ SELECT * FROM emp WHERE dept='SCOTT' |
| INDEX_DESC | /*+ INDEX_DESC(alias idx) */ | 索引降序掃描 | /*+ INDEX_DESC(emp pk_emp) */ SELECT * FROM emp WHERE dept='SCOTT' |
| INDEX_COMBINE | /*+ INDEX_COMBINE(alias idx1, idx2) */ | 位圖索引組合 | /*+ INDEX_COMBINE(emp sal_bmi, hiredate_bmi) */ SELECT * FROM emp WHERE sal<5000000 |
| INDEX_JOIN | /*+ INDEX_JOIN(alias idx1, idx2) */ | 索引連接訪問 | /*+ INDEX_JOIN(emp sal_hmi, hiredate_bmi) */ SELECT sal, hiredate FROM emp WHERE sal<60000 |
| INDEX_FFS | /*+ INDEX_FFS(alias idx) */ | 快速全索引掃描 | /*+ INDEX_FFS(emp in_empnam) */ SELECT * FROM emp WHERE dept='TEC305' |
| ADD_EQUAL | /*+ ADD_EQUAL(alias idx1, idx2) */ | 合併多單列索引 | /*+ ADD_EQUAL(emp in_dptno, in_empno, in_sex) */ SELECT * FROM emp WHERE empno='SCOTT' |
| Hint | 含義 | 範例 |
|---|
| ORDERED | 按 FROM 子句順序連接 | /*+ ORDERED */ SELECT ... FROM t1, t2, t3 WHERE t1.c=t2.c AND t2.c=t3.c |
| LEADING | 指定驅動表 | /*+ LEADING(t1) */ SELECT ... FROM t1, t2 WHERE ... |
| USE_NL | 嵌套循環連接（指定內表） | /*+ ORDERED USE_NL(emp) */ SELECT ... FROM dept, emp WHERE emp.dept=dept.dept |
| USE_MERGE | 排序合併連接 | /*+ USE_MERGE(emp, dept) */ SELECT * FROM emp, dept WHERE emp.dept=dept.dept |
| USE_HASH | 哈希連接 | /*+ USE_HASH(emp, dept) */ SELECT * FROM emp, dept WHERE emp.dept=dept.dept |
| DRIVING_SITE | 強制在指定站點執行（分布式） | /*+ DRIVING_SITE(dept) */ SELECT * FROM emp, dept@remote WHERE ... |
