Re: Cost Vs. Rules Based Optimizers

I think there are three important things to remember about the Cost Based Optimiser:

    Its purpose is to protect the database from the ignorant     end-user; not to help the developer. (IMHO)

    It assumes that the indexes you have created are notionally     correct for the application you are running.

    It works on statistics (largely averages) not detailed data.

Part of the designer's job is to be aware of the sizes of (numbers of rows in) tables, the data ratios between tables, and the query (join) requirements of the application. With this information in mind he should decide on sensible indexes and data representations that will allow the system to work efficiently.

If the indexes are sub-optimal, or the data representation is not appropriate, then the optimiser can be fooled into doing extremely stupid things.

Example (typical of an error I have often seen):

   An accounting system has a table of journal entries that are    either posted or unposted. The designer has cunningly indicated    this in a varchar2(1) column with the legal values 'P' and 'U'.

   At any moment, there are a few dozen unposted items, and a few    hundred thousand posted ones.

   Some commonly used programs are targetted at unposted items:    their optimal access path clearly starts on this flag and works    out to related tables, and there is an index to reflect this fact.

   The cunning, rule-based, coder consequently forces this index to    be used by downgrading the significance of other entry points to    these queries.

   If you switch to the cost-based optimiser, Oracle looks at such    queries, observes the possibility of using this indexed column    then notes that there are TWO POSSIBLE VALUES, so that (on average)    using this index will return half the rows in the table. Is it    going to use this index ? NO WAY: on this evidence (and given    that the coder has perhaps disabled other entry indexes) it might    even decide to do a full tablescan of the table.

   Result: your carefully crafted code soaks the machine. (Obviously    you would of course instantly spot this one, and fix it with a HINT.    Better still, you might change the column/code so that null and U are    the only legal values).

Conclusion:
Do not expect the CBO to help a pre-existing, competently-designed application to perform better. At best it will only limit the damage done by unconstrained end-user SQL.

-- 
Jonathan Lewis