Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Bad Generalisations by Don Burleson

Bad Generalisations by Don Burleson

From: Hemant K Chitale <hkchital_nospam_at_singnet_nospam.com.sg>
Date: Wed, 8 May 2002 21:23:37 +0800
Message-ID: <abb96m$b8f$1@violet.singnet.com.sg>


Some examples of bad (awfully misleading ?) generalisations by Don Burleson in the latest "Oracle Tips at Builder.com" [from Oracle Tips at Builder.com"
<Online#3.13744.48-wJ0Xs9GgV9aqydRR.1_at_newsletter.online.com>"]

To quote from the same email again
"Don Burleson has written 12 books, published over 70 articles in national
magazines, and is editor in chief of Oracle Internals. " Don't rule out the RBO

In Oracle9i, DBAs commonly use both the cost-based optimizer (CBO) and the rule-based optimizer (RBO) to derive the execution plan for Oracle SQL statements. While Oracle announced plans to retire the rule-based optimizer in the 1990s, it remains an important tool for optimizing Oracle SQL. The rule-based optimizer is very elegant in its simplicity, and it often makes faster execution choices than the CBO. In fact, Oracle Applications products used the RBO until the introduction of the Oracle Apps 11i product in 2001. It's only with the release of Oracle8i (8.1.6) that the CBO has become faster than the RBO in all cases.

While it's 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:

  a.. Always use the index: If you can use an index 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 doesn't require an index).

  b.. Always start 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.

  c.. Use full table scans as a last resort: The RBO isn't aware of Oracle parallel query and multiblock reads, and it doesn't consider the size of the table. Hence, the RBO dislikes full table scans and will only use them when no index exists.

  d.. Any index will do: The RBO will sometimes choose a less-than-ideal index to service a query. This is because the RBO doesn't have access to statistics that show the selectivity of indexed columns.

  e.. 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 commonly choose the
"wrong" index to access a table. This is because the RBO doesn't have
statistics to tell it the relative selectivity and cardinality of the indexes column.

Hemant K Chitale
http://hkchital.tripod.com Received on Wed May 08 2002 - 08:23:37 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US