Oracle Database Administration: The Essential Refe

Join operation hints

Hints in this category allow you to specify how to perform the join operation. The following list describes each hint in this category and provides an example of its use:

USE_NL

Forces the optimizer to perform a nested loop join, with the listed table as the inner table.

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

USE_MERGE

Forces the optimizer to perform a cascading merge join. The first two tables in the hint are joined using a merge join, and succeeding tables are joined, one by one, to the result set, using a merge join.

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

USE_HASH

Forces the optimizer to perform a cascading hash join. The first two tables in the hint are joined using a hash join, and succeeding tables are joined, one by one, to the result set, using a hash join.

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

DRIVING_SITE

Forces the optimizer to use a specific site for driving distributed queries.

SELECT /*+ DRIVING_SITE(dept) */ *
FROM emp,dept@drsite
WHERE emp.deptno = dept.deptno;