Cost-based Optimizers

• Quoting Bill Busch to All dated 09-22-94 ***
  > I am doing some research into cost based optimizers. My
  > experience has been limited to ORACLE (no sympathy please) which
  > does not have a decent cost-based optimizer (when ORACLE’s tech
  > support and education departments tell you not to use their
  > optimizer because it will generally hinder performance, it say a
  > lot about the quality or, lack there of, of their optimizer).
  

Well, cost based optimizers generally take more time and more overhead than rule based optimizers, and this is coupled with the fact that Oracle's cost based optimizers (must) rely on assumptions when table statistics have not been gathered. In other words, you should not consider the relative speeds of rule based and cost based optimization to be an indictment of the efficiency of the cost based algorithm.

> So, what attributes make up a good cost-based optimizer and how
> do the major vendors (ORACLE, INGRES, SYBASE, and INFORMIX)
> compare. I have heard that the most advance cost-based optimizer
> is included in INGRES. If so, what makes their optimizer so much
> better?

This question is akin to asking what makes a good C compiler/optimizer. Simply, the resulting code runs faster. A cost based optimizer generally works by using table/index statistics to calculate costs for possible execution paths, and then choosing the path with the lowest cost. The most important elements of cost optimization are:

1. The table/index weighting algorithm. (In many Oracle environments, this is shot in the foot by DBA's who switch on the cost optimizer without performing table/index analysis, and confounded further by poor modeling.)
2. The number of execution paths considered available. Ironically, this is a feature of rule-based optimization -- some compilers and environments are able to take short cuts or make assumptions to allow better execution paths.
3. The cost calculation algorithm. It is important that this be closely related to the actual cost of execution. It is important to note that this can be complicated or defeated by media or platform specific concerns, and does not obviate the need for good database tuning.