ORDERED hint

The ORDERED hint is one of the most powerful hints. It process the table of query in the sequential order listed in the FORM clause.
When the ORDERED hint is not used, Oracle may internally switch the driving table when compared to how tables are listed in the FROM clause (EXPLAIN PLAN can show how tables are accessed).

Syntax:

select /*+ ORDERED */ column1, …

Example1:

select /*+ ORDERED */ empno, ename, dept.deptno

from emp, dept

where emp.deptno = dept.deptno

and dept.deptno = 1

and emp.empno = 7747;

Emp and dept have been analyzed (using the cost-based optimizer) and there are no indexes on both the table, the emp table is accessed first and the dept table is accessed second.

Example2:

select /*+ ORDERED */ emp.empno, ename, dept.deptno, itemno

from emp, dept, orders

where emp.deptno = dept.deptno

and emp.empno = orders.empno

and dept.deptno = 1

and emp.empno = 7747

and orders.ordno = 40;;