Rule Based Optimizer   «Prev  Next»

Lesson 1

SQL rule-based Optimizer

This module will describe the inner workings of the rule-based optimizer and provide you with a set of guidelines for determining when it is best to employ rule-based SQL optimization.
By the time you complete this module, 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

Let us get started with an overview of rule-based optimization.

Has the Oracle SQL rule-based Optimizer been deprecated?

The Oracle SQL rule-based optimizer (RBO) has been deprecated in recent versions of Oracle Database. Oracle recommends using the cost-based optimizer (CBO) instead, as it generally provides better performance than the RBO. The CBO uses statistics about the data and the system to make more informed decisions about how to execute a query, whereas the RBO uses a set of predefined rules. The Rules Based Optimizer is still present in the latest version of Oracle Database for backward compatibility, but it will be removed in future releases.

What are the advantages of the Cost Based Optimizer over the Rules Based Optimizer in Oracle

The cost-based optimizer (CBO) has several advantages over the rule-based optimizer (RBO) in Oracle:
  1. More accurate: The CBO uses statistics about the data and the system to make more informed decisions about how to execute a query, while the RBO uses a set of predefined rules. This means that the CBO can make more accurate decisions that result in better performance.
  2. Better performance: The CBO can choose from a wider range of execution plans than the RBO, which can result in better performance for complex queries.
  3. Better handling of new features: The CBO is better equipped to take advantage of new features and optimizations that have been added to Oracle Database, whereas the RBO does not benefit from these advancements..
  4. Better handling of dynamic data: The CBO can adapt to changes in the data distribution and system configuration, whereas the RBO does not.
  5. Better handling of partitioned tables and indexes: The CBO can efficiently process partitioned tables and indexes, while the RBO might not be able to use them.
  6. Better handling of complex subqueries and joins: The CBO can handle complex subqueries and joins more efficiently than the RBO.
  7. Better handling of parallel execution: The CBO can make decisions about parallel execution that are based on the current system state and data distribution, while the RBO cannot.
  8. Better handling of indexes: The CBO can select the most appropriate index to use while the RBO is limited on what indexes it can select and when