Lesson 1

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.

Yes, the Oracle SQL "rule-based optimizer" (RBO) has been deprecated. Oracle deprecated the rule-based optimizer starting with Oracle 9i, which was released in the early 2000s. From Oracle 10g onwards, the focus shifted entirely to the "cost-based optimizer" (CBO), which uses statistics about the data to make optimization decisions. The cost-based optimizer offers several advantages over the rule-based approach by considering the actual data distribution and other factors, such as CPU and I/O costs, to determine the most efficient way to execute a query. It requires that statistics on database objects be regularly updated for optimal performance. Oracle no longer supports the rule-based optimizer, and all optimization is now done using the cost-based optimizer. Oracle encourages users to migrate their systems to use the cost-based optimizer to benefit from performance improvements and new features supported only by the CBO.

The shift from the Rule-Based Optimizer (RBO) to the Cost-Based Optimizer (CBO) in Oracle databases marked a significant advancement in how queries are processed and optimized. Here are the advantages of each approach:
1) Advantages of the Cost-Based Optimizer (CBO)

1. Data-Driven Decisions: The CBO makes decisions based on the actual data in the database. It uses statistics about the data such as table sizes, number of rows, data distribution, and index selectivity to estimate the cost of various query execution plans. This results in more efficient query execution tailored to the current state of the data.
2. Dynamic Optimization: The CBO can adapt to changing data conditions. As data grows or changes, updated statistics can lead to different execution plans that are better suited to the new data characteristics. This dynamic nature helps maintain optimal performance over time.
3. Comprehensive Query Optimizations: The CBO supports a wider range of optimizations such as query transformations (e.g., predicate pushdown, subquery flattening), join methods (e.g., hash join, nested loop, sort merge), and access paths (e.g., full table scans, index scans). This comprehensive approach allows for more sophisticated decision-making.
4. Better Use of Indexes: The CBO can more effectively determine when to use an index based on the estimated cost, which includes factors like I/O, CPU usage, and network overhead. It can also decide between using different types of indexes or a combination of them.
5. Parallel Execution: The CBO is better at deciding when to use par allel query execution to speed up processing by distributing the workload across multiple processors. This is crucial for large databases and complex queries.

2) Advantages of the Rule-Based Optimizer (RBO)

1. Simplicity and Predictability: The RBO follows a set of predefined rules to choose an execution plan. The rules are straightforward and deterministic, making the optimizer’s behavior predictable. This predictability was sometimes preferred in environments where consistent performance was critical, and any variability in execution plans could lead to unexpected degradation.
2. Less Overhead: Since the RBO does not rely on statistics, it avoids the overhead of collecting and maintaining detailed statistics on the database objects. This could be advantageous in scenarios where maintaining statistics is not feasible.
3. Performance in Small Databases: For very small databases or systems with limited resources, the simplicity of the RBO might yield better performance, as the overhead of computing costs might not justify the use of the CBO.

Conclusion Despite the initial advantages of the RBO in terms of simplicity and predictability, the benefits of the CBO's dynamic and data-driven approach far outweigh those of the RBO, especially in modern databases where data volume, variety, and velocity are much greater. The ability of the CBO to optimize queries based on actual data characteristics generally results in better performance and scalability, which is why Oracle and other modern database systems have moved towards exclusively using cost-based optimization strategies.