Call for Oracle support & training (800) 766-1884
Free Oracle Tips

Home
Corporate Oracle Training
Custom Oracle Training
Oracle New Features Training
Advanced Oracle DBA Classes
Oracle Tuning Courses
Oracle Tips & Tricks
Oracle Training Links
Oracle Training Links
Oracle Training Links

We are top for USA Oracle Training Clients

 

Free Oracle Tips


 
HTML Text AOL

Free Oracle App Server Tips


 
HTML Text

Oracle support

Oracle training

Oracle tuning

Rednecks!

Remote Oracle

Custom Oracle Training

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

 

 

 
 
 

Oracle performance tuning book

 

 

Oracle performance tuning software

 
Oracle performance tuning software
 
Oracle performance Tuning 10g reference poster
 
Oracle training in Linux commands
 
Oracle training Excel
 
 
 
 
 
email BC:


Copyright © 1996 -  2014 by Burleson Inc. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.