Donald K. Burleson Oracle Tips

Inside the rule-based SQL optimizer

Today in Oracle9i, both the cost-based optimizer and the rule-based optimizer are commonly used to derive the execution plan for Oracle SQL statements.  Back in the 1990’s Oracle announced plan to retire the rule-based optimizer, but it remains today as an important tool for optimizing Oracle SQL.

The rule-based optimizer is very elegant for its simplicity and often made faster execution choices than the CBO. In fact, Oracle Applications products used the RBO until 2001 when the Oracle Apps 11i product was introduced. It is only with the release of Oracle8i (8.1.6) that the CBO has become faster than the RBO in all cases.

While it is very tempting to go into the relative advantages of the RBO and CBO within each successive release of Oracle, we can make some general observations about the characteristics of the rule-based optimizer:

• Always use the Index - If an index can be used to access a table, choose the index. Indexes are always preferred over a full-table scan of a sort merge join ( a sort merge join does not require an index).

• Always starts with the driving table - The last table in the from clause will be the driving table – For the RBO, this should be the table that chooses the least amount of rows. The RBO uses this driving table as the first table when performing nested loop join operations.

• Full-table scans as a last resort - The RBO is not aware of Oracle parallel query and multi-block reads, and does not consider the size of the table. Hence, the RBO dislikes full-table scans and will only use them when no index exists.

• Any index will do - The RBO will sometimes choose a less than ideal index to service a query. This is because the RBO does not have access to statistics that show the selectivity of indexed columns.

• Simple is sometimes better - Prior to Oracle8i, the RBO often provided a better overall execution plan for some databases.

The biggest shortcoming of the RBO is that it will common choose the “wrong” index to access a table.  This is because the RBO does not have statistics to tell it the relative selectivity and cardinality of the indexes column.

If you like Oracle tuning, you might enjoy my latest book “Oracle Tuning: The DefinitiveReference” by Rampant TechPress. (I don’t think it is right to charge a fortune for books!) and you can buy it right now at this link:

http://www.rampant-books.com/book_1002_oracle_tuning_definitive_reference_2nd_ed.htm