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.

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;