Oracle Database Administration: The Essential Refe

Join order hints

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

ORDERED

Forces the optimizer to drive the join order based upon the order in which the tables are listed in the WHERE clause.

SELECT /*+ ORDERED */  emp,dname
FROM emp, dept
WHERE emp.deptno = dept.deptno;

In this case, the optimizer would perform a full table scan on the EMP table and perform a nested loop to query the DEPT table. This is the opposite order from that in which the rule-based optimizer would drive the tables.

STAR

Forces the optimizer to perform a star query. In order for this to work, you must have at least three tables in the query, and the largest table should have a concatenated index with at least three columns.

SELECT /*+ STAR */ fact.a, fact.b, dim1.c, dim2.d, dim3.e
FROM fact, dim1, dim2, dim3
WHERE fact.i1 = dim1.i1
AND fact.i2 = dim2.i2
AND fact.i3 = dim3.i3;