SQL where clause order can change performance

Many people believe that the Oracle cost-based SQL optimizer does not consider the order that the Boolean predicates appear in the WHERE clause.

However, there is some evidence that this is not completely true, as evidenced by the ordered_predicates SQL hint.

The 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:

• Subqueries are evaluated before the outer Boolean conditions in the WHERE clause.

• All Boolean conditions without built-in functions or subqueries are evaluated in reverse from the order they are found in the WHERE clause, with the last predicate being evaluated first.

• Boolean predicates with built-in functions of each predicate are evaluated in increasing order of their estimated evaluation costs.

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 table.

• 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 each operation.
•  The optimizer than shuffles the predicates to get the smallest total estimated CPU cost.
•  The "ordered predicates" hint (deprecated in 10gr2) will change optimizer_cost_model=io, invalidating CPU costing.
  

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.

Also note that transitive closure in the WHERE clause effects performance.