Rule Based Optimizer   «Prev  Next»

Lesson 9

Rule-based Optimizer Conclusion

As we can clearly see, the rule-based optimizer is quite complex, and there are many skills required to tune rule-based queries.

Rule-based Optimization (RBO) Obsolescence

Rule-based Optimization as a functionality is no longer supported and still existed in Oracle 10g Release 1, but is an unsupported feature. No code changes have been made to RBO and no bug fixes are provided. Oracle supports only the query optimizer, and all applications running on Oracle Database 10g Release 1 (10.1) should use that optimizer.
Some consequences of the desupport of RBO are:
CHOOSE and RULE are no longer supported as OPTIMIZER_MODE initialization parameter values and a warning is displayed in the alert log if the value is set to RULE or CHOOSE. The functionalities of those parameter values still exist but will be removed in a future release.

Rule-based Optimizer Summary

  1. The rule-based optimizer is the oldest SQL optimizer, but it is sometimes faster than cost-based optimization.
  2. The rule-based optimizer will perform better than cost-based for table joins of more than three tables.
  3. The rule-based optimizer may be better for queries that have very dynamic data, or where the statistics may be obsolete.
  4. The rule-based optimizer does not choose indexes very well, and will sometime choose a poor index. This can be corrected by adding an index hint, invalidating an index, or by re-sequencing the items in the WHERE clause.
  5. The order of the table names in the FROM clause determines the driving table.
  6. The table that returns the smallest number of rows should be placed last in the FROM clause.
  7. The order of condition in the WHERE clause tells the rules-based optimizer which index to use. Boolean in the WHERE clause should be ordered with the most selective condition last.
Now you should be able to:
  1. Describe the major features of the rule-based optimizer
  2. List the advantages and disadvantages of the rule-based optimizer
  3. Correctly order table names in the FROM clause
  4. Correctly order items in the WHERE clause
  5. Rewrite complex Boolean logic with the UNION operator
  6. Evaluate and tune rule-based queries

New terms

Here are some terms from this module that may have been new to you:
  1. Driving table: This is the table used by the SQL optimizer in the initial step of execution.
  2. host variables: This is a variable that is used inside PL/SQL.
  3. Nested loop: This is a join method that queries the driving table and then probes the other tables via an index.
  4. UNION operator: This is an SQL operator that joins the result sets of multiple SELECT statements.
In the next module, you will take a closer look at the cost-based optimization mode.

Rule Based Optimizer - Quiz

Before you go on, click the Quiz link below to see how well you understand some of the concepts from this module.
Rule Based Optimizer - Quiz