In brief the rule-based method means that when executing a query the database must follow certain predefined rules and matter what data is stored in affected database
tables. The cost-based method means the database must decide which query execution plan to choose using best guess approach that takes into account what data is stored
in db.
A long time ago the only optimizer in the Oracle database was the Rule-Based Optimizer (RBO). Basically the RBO used a set of rules to determine how to execute a
query. If an index was available on a table the RBO rules said to always use the index. There are some cases where the use of an index slowed down a query. For example
assume someone put an index on the GENDER column which holds one of two values MALE and FEMALE.
Then someone issues the following query:
SELECT * FROM emp WHERE gender ‘FEMALE’; If the above query returned approximately 50 of the rows then using an index would actually slow things down. It would be
faster to read the entire table and throw away all rows that have MALE values. Experts in Oracle query optimization have come to a rule of thumb that says if the
number of rows returned is more than 5-10 of the total table volume using an index would slow things down. The RBO would always use an index if present because its
rules said to.
It became obvious that the RBO armed with its set of discrete rules did not always make great decisions. The biggest problem with the RBO was that it did not take the
data distribution into account. So the Cost-Based Optimizer (CBO) was born. The CBO uses statistics about the table its indexes and the data distribution to make
better informed decisions.
Using our previous example assume that the company has employees that are 95 female and 5 male. If you query for females then you do not want to use the index. If you
query for males then you would like to use the index. The CBO has information at hand to help make these kind of determinations that were not available in the old RBO.
What is Cost-Based Optimization?
The Oracle cost-based optimizer is designed to determine the most efficient way to carry out a SQL statement, but it can’t reach do this without good, up-to-date
statistical information on the data being accessed. The optimizer can use a rules-based approach to work without statistical information, but this approach is less
intelligent than the cost-based approach. With the rules-based approach, the optimizer chooses an execution plan based a set of rules about what types of operations
usually execute faster than other types. With the cost-based approach, the optimizer factors in statistical information about the contents of the particular schema
objects (tables, clusters, or indexes) being accessed.
Rule Based Optimizer Obsolescence
The Rule Based Optimizer (RBO) is now obsolete in Oracle 10g. The functionality is still present but no new functionality has been included in it and it is no longer
supported by Oracle. It is only present to provide backwards compatibility during the migration to the query optimizer (Cost Based Optimizer). The results of this
osolescence are:
– The CHOOSE and RULE options for the OPTIMIZER_MODE parameter still exist but are no longer supported.
– The default value for the OPTIMIZER_MODE parameter is ALL_ROWS.
– The CHOOSE and RULE optimizer hints still exist but are no longer supported.
– Code requiring the RBO must be migrated to use the query optimizer.
ozwenolivercoelho 