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;

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;

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;

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;