In large applications that juggle tons of data, it isn’t enough to get a query to run without errors. The query must also perform well — get the results in fastest possible time, using the least possible resources.
This is where Oracle’s inbuilt optimizers come into play. Till Oracle 9i, Oracle server provided the options of CBO (cost-based optimizer) and RBO (rule-based optimizer). RBO has been desupported by Oracle 10G onwards, but it is still worth knowing about to understand the evolution of Oracle’s optimization techniques.
This post explores the key differences between CBO and RBO.
Oracle’s optimizer determines the most efficient way to execute a SQL statement, and gives as output an "optimizer plan" that describes the optimum method of execution.
CBO and RBO differ in how they build the optimizer plan, and in various other ways. For a summary of these differences, a tabulated comparison of Oracle’s two optimizers: CBO and RBO.
| CBO | RBO | |
| Stands for | Cost-Based Optimizer | Rule-Based Optimizer |
| History | Introduced in Oracle 7 (year 1992). Evolved with every Oracle release since then, and has now replaced RBO. | Introduced in Oracle 5 (year 1985), as the only optimizer mode at that time. Co-existed with CBO Oracle 7 onwards. Phased out formally Oracle 10G onwards (year 2003). |
| Overall Approach | Chooses an execution plan that is most cost-effective (in terms of I/O, CPU, memory, etc.) | Chooses an execution plan based on a predefined set of rules (picks the lowest-ranked access path from a list of access paths) |
| Impact of Data Distribution | Takes into account data volume and distribution while determining the access path | Does not take into account data volume and distribution while determining the access path |
| Stats Gathering Required? | Yes. For optimal CBO execution plans, stats should be updated and realistic. | No. Stats gathering has no impact on RBO execution plans. |
| Impact of FROM clause table order | Table order in FROM clause does not matter. CBO determines the best plan based on stats and other information available to it. | Tables are processed from right to left in the FROM clause |
| Impact of WHERE clause predicate order | Predicate order in WHERE clause does not matter. CBO determines the best plan based on stats and other information available to it. | WHERE clause predicates are processed from right to left |
| Understands SQL Hints? |
CBO understands SQL hints. Any well-formed SQL hint makes Oracle use the CBO, even if the optimizer mode is set otherwise. The only exception is the/*+ RULE */ hint. |
RBO does not understand SQL hints. The /*+ RULE */ hint makes Oracle use the RBO, even if the optimizer mode is set otherwise. |
| Limitations | CBO is evolving with each Oracle release to become more robust and intelligent: a side-effect of this is potential performance regression (i.e. execution plan changes for the worse) after a database upgrade. Oracle offers guidelines for fixing sub-optimal plans in this scenario. | RBO does not produce optimal plans in several scenarios — a classic case is one where a full-scan is more cost-effective than index-based scan, but the RBO is set to always use an index.
RBO does not work with newer features of Oracle such as partitioned tables, function-based indexes, query rewrite with materialized views, etc. |
| Further Reading (9.2 SQL Reference) | Cost-Based Optimizer | Rule-Based Optimizer |
{ 4 comments… read them below or add one }
Excellent summary.
Excellent
Good comparison
dangg