Oracle leading vs. ordered hints

The "ordered" hint is extremely useful for cutting-down query parse time and ensuring proper table join order for static tables and queries.

• Good article on using the ordered hint
• Ault on the ordered hint

/* LEADING */ Hint – specifies the set of tables to be used as the prefix in the execution plan.

• The "leading" hint is ignored if the tables specified cannot be joined first in the order specified
• If you specify two or more conflicting LEADING hints – all of them are ignored
• The ORDERED hint overrides all LEADING hints

/* ORDERED */ Hint – Oracle joins tables in the order in which they appear in the FROM clause

• The optimizer normally chooses the order in which to join the tables, but it's time-consuming and wrong if you have bad CBO stats (especially histograms)
• You may want to specify the "ordered" hint if you know something about the number of rows selected from each table that the optimizer does not

The Oracle 10g documentation notes the difference between the "ordered" and "leading" hints:

The LEADING hint causes Oracle to use the specified table as the first table in the join order.

If you specify two or more LEADING hints on different tables, then all of them are ignored. If you specify the ORDERED hint, then it overrides all LEADING hints.