Rule Based Optimizer   «Prev  Next»

Lesson 6Positioning statements in the WHERE clause
ObjectiveCorrectly order items in the WHERE clause

Positioning Statements in WHERE clause

In order to improve performance of queries, you need to carefully examine how Oracle processes items in the WHERE clause.
When building an execution plan, Oracle parses the SQL from the bottom of the SQL statement, in reverse order. Therefore, the most restrictive Boolean expression should be last in the WHERE clause.
For example, consider the following query. Here we see two Boolean conditions in the WHERE clause, and we must sequence them in their order of selectivity.

Ordering the WHERE clause

When tuning items in the WHERE clause, we always need to check to ensure that there is a usable index on all of the Boolean conditions. This technique of re-sequencing items will only work if there is an index on each of the items in the WHERE clause.
In the next lesson, you will learn how the UNION operator can help you deal with complex Boolean logic.

Positioning Statements Where Clause-Exercise

Click the Exercise link to try an exercise in the ordering of Boolean predicates.
Positioning Statements Where Clause-Exercise