Hints in this category allow you to specify which access path to use. The following list describes each hint in this category and provides an example of its use:
Forces the optimizer to perform a full table scan on the specified table.
SELECT /*+ FULL(emp) */ ename,dname FROM emp, dept WHERE emp.deptno = dept.deptno;
Forces a table scan using the ROWID for the specified table.
SELECT /*+ ROWID(emp) */ ename,dname FROM emp, dept WHERE emp.deptno = dept.deptno;
Forces the optimizer to use a cluster scan for the specified table. Obviously, this can only apply to clustered tables.
SELECT /*+ CLUSTER(emp) */ ename,dname FROM emp, dept WHERE emp.deptno = dept.deptno;
Forces the optimizer to use a cluster hash scan to access the specified table. Obviously, this can only apply to clustered tables.
SELECT /*+ HASH(emp) */ ename,dname FROM emp, dept WHERE emp.deptno = dept.deptno;
Tells the optimizer to transform a NOT IN subquery into a hash anti-join.
SELECT /*+ HASH_AJ */ ename FROM emp WHERE deptno NOT IN (10,20);
Forces the optimizer to convert a correlated EXISTS subquery into a hash semi-join.
SELECT /*+ HASH_SJ */ ename FROM emp WHERE EXISTS (SELECT 'x' FROM dept WHERE emp.deptno = dept.deptno);
Forces the optimizer to use an index scan on the specified index.
SELECT /*+ INDEX(emp emp_pk) */ * FROM emp WHERE empno = 7900;
Forces the optimizer to use an index scan on the specified index. It further specifies that the index range scan be performed in ascending order. In Oracle7 and Oracle8, this is the current practice, so this hint works exactly like the INDEX hint.
SELECT /*+ INDEX_ASC(emp emp_pk) */ * FROM emp WHERE empno = 7900;
There are two forms of this hint. The first form specifies only a table. In this case, the optimizer will use whatever Boolean combination of bitmap indexes it determines is best for the query.
SELECT /*+ INDEX_COMBINE (dept) */ * FROM dept WHERE dname = 'SALES' AND loc = 'CHICAGO';
The second form specifies one or more bitmap indexes that should be included in the Boolean combination.
SELECT /*+ INDEX_COMBINE (dept dept_loc_i) */ * FROM dept WHERE dname = 'SALES' AND loc = 'CHICAGO';
Forces the optimizer to perform an index scan on the specified index. It further specifies that the index range scan be performed in descending order. This hint can only be used with SQL statements that access one table.
SELECT /*+ INDEX_DESC(emp emp_pk) */ * FROM emp WHERE empno = 7900;
Causes a fast full index scan rather than a full table scan.
SELECT /*+ INDEX_FFS(emp emp_pk) */ * FROM emp WHERE empno = 7900;
Transforms a NOT IN subquery into a merge anti-join.
SELECT /*+ HASH_AJ */ ename FROM emp WHERE deptno NOT IN (10,20);
Transforms a correlated EXISTS subquery into a merge semi-join.
SELECT /*+ HASH_SJ */ ename FROM emp WHERE EXISTS (SELECT 'x' FROM dept WHERE emp.deptno = dept.deptno);
Explicitly causes the optimizer to choose an access plan that merges scans on several single-column indexes. You must specify at least two, and no more than five, indexes.
SELECT /*+ AND_EQUAL (emp emp_deptno emp_sal) */ * FROM emp WHERE deptno = 20 and sal = 3000;
Forces the optimizer to convert a query with an OR statement into a UNION ALL.
SELECT /*+ USE_CONCAT /* * FROM emp WHERE deptno = 20 OR sal = 3000;
In this case, the optimizer would convert this query into:
SELECT * FROM emp WHERE deptno = 20 UNION ALL SELECT * FROM emp WHERE sal = 3000;