However, there is
some evidence that this is not completely true, as evidenced by
the ordered_predicates SQL hint.
ordered_predicates hint (deprecated in 10g r2) is specified in the WHERE clause of a
query and is used to specify the order in which Boolean
predicates should be evaluated. In the absence of
ordered_predicates (which is deprecated in Oracle 10g
and beyond), Oracle uses the following steps to evaluate
the order of SQL predicates:
The problem is that the Oracle SQL optimizer might re-arrange
the order of the where clause predicates, causing
sub-optimal execution plans.
If you experiment with changing the order of predicates in the
WHERE clause you will notice changes to the execution plan.
It's also been noted that Oracle follows different goals when
applying predicates using I/O costing vs. CPU costing.
When optimizing for minimizing I/O:
The decision tree factors are estimated physical disk reads.
The computed estimated cost is the "selectivity"
as expressed as the percentage of rows in the
The first predicate will be the most selective, such as to
move through execution with the smallest amount of transient row
sets as possible.
When optimizing to minimize computing resources: (CPU Costing)
- The decision tree estimates the number of computing units for
- The optimizer than shuffles the
predicates to get the smallest total estimated
- The "ordered predicates" hint
(deprecated in 10gr2) will change
optimizer_cost_model=io, invalidating CPU
In sum, while the order of WHERE clause
predicates should not make a difference, there
is evidence that the order of WHERE clause
predicates makes a difference.
that transitive closure in the WHERE clause