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.

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.

The same concepts do not directly apply to the Cost-Based Optimizer (CBO) in Oracle. The CBO does not rely on the order of predicates in the `WHERE` clause for determining execution plans. Instead, it makes decisions based on statistics.
Detailed Explanation:

• Rule-Based Optimizer (RBO)
  • Predicate order matters.
  • The optimizer reads the SQL in reverse order (bottom to top of WHERE clause).
  • Therefore, more selective (restrictive) predicates should appear last in the WHERE clause to influence index usage.
  • The example in your image shows this: 'AB Negative' is rare → should appear last.
• Cost-Based Optimizer (CBO)
  • Predicate order does NOT matter.
  • Oracle evaluates object statistics (number of rows, distinct values, histograms, etc.).
  • CBO uses cost estimates to determine the most efficient execution plan, regardless of SQL text order.
  • It may even reorder joins and predicates internally for better performance.

Best Practice for CBO:

• Gather accurate statistics using DBMS_STATS.
• Make sure histograms exist on skewed columns (like Blood_type if AB Negative is rare).
• Use bind-aware queries if data skew is involved.
• Focus on query structure, index availability, and statistics, not predicate order.

Conclusion:

• In RBO, predicate order impacts performance (more selective last).
• In CBO, predicate order is ignored, and statistics drive decisions.

Here's a clear side-by-side comparison of Rule-Based Optimizer (RBO) vs Cost-Based Optimizer (CBO) behavior in Oracle:

Aspect	Rule-Based Optimizer (RBO)	Cost-Based Optimizer (CBO)
Decision Basis	Uses predefined rules for execution plans	Uses statistics (table, index, column) to estimate cost
Predicate Order Importance	Yes – most selective predicate should come last	No – predicate order in SQL is ignored
Join Order	Follows fixed rules (e.g., nested loops)	Reorders joins based on cost estimates
Index Selection	Picks the first matching index based on rules	Chooses index based on selectivity and cost
Optimization Mode	Rule-based: no cost model involved	Cost-based: uses cost = I/O + CPU + network estimations
Statistics Usage	No statistics are used	Relies heavily on table and column statistics
Histograms	Not applicable	Can use histograms to understand column data distribution
Hint Sensitivity	Uses rule-based hints like `RULE`, `INDEX`	Honors CBO hints like `ALL_ROWS`, `FIRST_ROWS(n)`
Deprecated?	✅ Deprecated as of Oracle 10g	✅ Default and recommended optimizer since Oracle 10g
Real-World Suitability	Suitable for small, static systems	Ideal for complex, dynamic workloads

Summary:

• RBO is like a “checklist” – it applies hard-coded rules and is sensitive to SQL structure.
• CBO is like a “calculator” – it makes decisions based on numeric cost estimations from real data.

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