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:
- Describe the major features of the rule-based optimizer
- List the advantages and disadvantages of the rule-based optimizer
- Correctly order table names in the FROM clause
- Correctly order items in the WHERE clause
- Rewrite complex Boolean logic with the UNION operator
- Evaluate and tune rule-based queries
Let us get started with an overview of rule-based optimization.
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.