Oracle Database Administration: The Essential Refe

Access method hints

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:

FULL

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;

ROWID

Forces a table scan using the ROWID for the specified table.

SELECT /*+ ROWID(emp) */  ename,dname
FROM emp, dept 
WHERE emp.deptno = dept.deptno;

CLUSTER

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;

HASH

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;

HASH_AJ

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);

HASH_SJ

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);

INDEX

Forces the optimizer to use an index scan on the specified index.

SELECT /*+ INDEX(emp emp_pk) */ *
FROM emp 
WHERE empno =  7900;

INDEX_ASC

Forces the optimizer to use an index scan on the specified index. It further specifies that the index range ...