ordered Predicates hint Tips

Using the ordered_predicates hint

The ordered_predicates hint 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, Oracle uses the following steps to evaluate the order of SQL predicates:

1. Subqueries are evaluated before the outer Boolean conditions in the WHERE clause.
   
2. 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.
   
3. Boolean predicates with built-in functions of each predicate are evaluated in increasing order of their estimated evaluation costs.

You can override these default evaluation rules using the ordered_predicates hint, and your WHERE clause items will then be evaluated in the order that they appear in the query. The ordered_predicates hint is commonly used in cases where a PL/SQL function is used in the WHERE clause of a query. It is also very useful in cases where you know the most restrictive predicates and you want Oracle to evaluate these first.

Usage note:

You cannot use the ordered_predicates hint to preserve the order of predicate evaluation on index keys.

Get the Complete Oracle SQL Tuning Information  The landmark book "Advanced Oracle SQL Tuning  The Definitive Reference"  is filled with valuable information on Oracle SQL Tuning. This book includes scripts and tools to hypercharge Oracle 11g performance and you can buy it for 30% off directly from the publisher.