ORDERED Hint

Learned something new from Oracle product documentation. Its about ORDERED Hint.causes Oracle to join tables in the order in which they appear in the FROM clause. If you omit the ORDERED hint from a SQL statement performing a join, then the optimizer chooses the order in which to join the tables. A quick demonstration about that.

test@9iR2> set autotrace traceonly explain;
test@9iR2>
test@9iR2>
test@9iR2> SELECT e.FIRST_NAME, e.last_name, d.department_name, j.job_title
  2  from hr.employees e,
  3       hr.departments d,
  4       hr.jobs j
  5  WHERE e.DEPARTMENT_ID = d.DEPARTMENT_ID
  6  and   e.job_id = j.job_id
  7  /

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=8 Card=106 Bytes=7420)
   1    0   HASH JOIN (Cost=8 Card=106 Bytes=7420)
   2    1     HASH JOIN (Cost=5 Card=107 Bytes=5778)
   3    2       TABLE ACCESS (FULL) OF 'JOBS' (Cost=2 Card=19 Bytes=513)
   4    2       TABLE ACCESS (FULL) OF 'EMPLOYEES' (Cost=2 Card=107 Bytes=2889)
   5    1     TABLE ACCESS (FULL) OF 'DEPARTMENTS' (Cost=2 Card=27 Bytes=432)

Now using ORDERED hint causes Oracle to join tables in the order in which they appear in the FROM clause

test@9iR2> SELECT /*+ ordered */ e.FIRST_NAME, e.last_name, d.department_name, j.job_title
  2  from hr.employees e,
  3       hr.departments d,
  4       hr.jobs j
  5  WHERE e.DEPARTMENT_ID = d.DEPARTMENT_ID
  6  and   e.job_id = j.job_id
  7  /

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=8 Card=106 Bytes=7420)
   1    0   HASH JOIN (Cost=8 Card=106 Bytes=7420)
   2    1     HASH JOIN (Cost=5 Card=106 Bytes=4558)
   3    2       TABLE ACCESS (FULL) OF 'EMPLOYEES' (Cost=2 Card=107 Bytes=2889)
   4    2       TABLE ACCESS (FULL) OF 'DEPARTMENTS' (Cost=2 Card=27 Bytes=432)
   5    1     TABLE ACCESS (FULL) OF 'JOBS' (Cost=2 Card=19 Bytes=513)