| Lesson 3 | Strengths of rule-based SQL optimization |
| Objective | List the benefits of the rule-based optimizer. |
Advantages of SQL Rule-Based Optimization
The Oracle "Rule-based Optimizer" (RBO) has been deprecated. Oracle began deprecating the RBO with the release of Oracle 9i, which was introduced in the early 2000s. Since then, Oracle has focused on the "Cost-based Optimizer" (CBO), which uses statistical information about the data to determine the most efficient way to execute SQL queries.
The Rule-based Optimizer was officially desupported in Oracle 10g, making the Cost-based Optimizer the standard for query optimization in Oracle databases. Oracle's emphasis on the CBO reflects its greater flexibility and efficiency in handling diverse and complex database environments, particularly as databases scale in size and complexity. Oracle recommends that users rely on the CBO and maintain up-to-date statistics for optimal performance.
Benefits of Rule-based Optimization
We need to remember to separate the hype from the reality of the SQL optimizers. While Oracle publicly states that the cost-based optimizer[1] is superior to rule-based optimization, experienced DBAs know that the rule-based optimizer will often make a faster execution plan. The safest way to test SQL is to always try the rule hint whenever you are experimenting with alternative execution plans. After all, even in Oracle8, Oracle application products continue to utilize the rule-based optimizer. While the cost-based optimizer may eventually replace the rule-based method, it is not dead by any means.
Rule-based SQL optimizer more efficient than Cost-based optimizer
Because of the simplicity of the rule-based SQL optimizer, the rule-based optimizer
often makes more efficient decisions about an execution plan than the cost-based optimizer.
The rule-based optimizer normally makes better decisions about the access path under the following conditions:
Complex equi-joins of more than three tables
The rule-based optimizer will perform better than the cost-based for table joins of more than three tables.
The cost-based optimizer has a very bad habit of performing a full-table scan on a table whenever more than three tables are joined together.
The rule-based optimizer normally makes better decisions about the access path under the following conditions:
Complex equi-joins of more than three tables
The rule-based optimizer will perform better than the cost-based for table joins of more than three tables.
The cost-based optimizer has a very bad habit of performing a full-table scan on a table whenever more than three tables are joined together.
Inadequate or obsolete table and index Statistics exist
The rule-based optimizer may be better for queries that have very dynamic data, and the statistics may be obsolete. In a cost-based environment where the data characteristics are rapidly changing, the rule-based optimizer may make a better access decision. The cost-based optimizer is improving with every release of Oracle. If it continues to improve, rule-based optimization will probably become obsolete. However, this is still a long time off, since even many Oracle8 applications written by Oracle (Oracle Financials, GEMMS), still use the rule-based optimizer
Now that you know two situations where it’s a good idea to use the rule-based optimizer, let’s look at instances when the rule-based optimizer is not the best solution.
Now that you know two situations where it’s a good idea to use the rule-based optimizer, let’s look at instances when the rule-based optimizer is not the best solution.
Define the cost-based optimizer using 2 sentences.
[1]
Cost-Based Optimizer (CBO): The Oracle Cost-Based Optimizer (CBO) is a component of the Oracle database that analyzes various possible execution plans for an SQL query. It leverages statistics about the data (e.g., table size, data distribution) to estimate the "cost" of each plan and chooses the one it deems most efficient.
