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;