Re: Rule vs. Cost

IMO, the supplier just choose the easy way out. Admitted, the optimizer was changed (and it is a pity you don't specify the version you are using). However, where it goes wrong is quite predictable: usually applications are tested with small amounts of records. This usually results in the ratio between lookup and fact tables being completely different from a production system and presto... As soon as a realistic number is reached CBO generates completely different plans, and there you are. Also, you should not forget you need to analyze tables frequently.

Hth,

--
Sybrand Bakker, Oracle DBA
jawa <Jim.Wadas_at_motorola.com> wrote in message news:807c6u$52f$1_at_schbbs.mot.com...
> Rule-based optimization runs more efficiently than cost on our
application.
> The supplier told us that the version 7 optimizer has problems. Is this
> true, or is it that their application was not written by folks
knowledgeable
> of the hints and tricks designed to take full advantage of cost-based
> optimization? For instance, I ran a small benchmark report on the schema
> and found: (1) >40 tables with no index on them at all (even small tables
> without an index become the driving table), and (2) >100 foreign keys
> missing child index references (all having the potential to put locks on
the
> parent table). Could these contribute to a cost-based scenario that would
> perform poorly, or is it true that version 7's optimizer has problems like
> the supplier alleges? Has anyone else experienced a similar case study?
>
> --
> Jim Wadas
> Information Technology Solutions and Services (ITSS)
> Motorola Systems Solutions Group (SSG)
> Scottsdale, AZ 85257
> (480) 441-8196
> Jim.Wadas_at_motorola.com
>
>
>
Received on Mon Nov 08 1999 - 16:08:51 CST