Re: cost base optimization

Andreas, What were the parameters you used to analyze the schema? You need a large enough sample size to get good statistics or you see the behavior you mentioned plus in some cases the CBO will just pick a bad path. Using the CBO does not relieve you from the responsibility of having to tune your SQL. It just changes the statements you need to tune.

If the SQL in question uses constants in the where clause against indexed columns then histograms can be very useful. Otherwise the CBO provides a wealth of hints to influence the access path.

Dave Marshall <dmarsh_at_istar.ca> wrote in article <8Jye2.602$n4.5487803_at_WReNphoon2>...
> Hi,
>
> If a table is relatively small and/or the index is not
> very selective, a full table scan can actually be
> faster than using an index lookup. The cost based
> optimizer therefore may choose a FTS over an index in
> some situations. The rule based optimizer always tries
> to use indexes if possible - a FTS is considered the
> least efficient way to retrieve records.
>
> Of course, if you find that CBO isn't choosing the
> optimal execution plan, you can always overrule
> individual statements by using hints.
>
> Good luck,
>
> Dave Marshall
>
>
>
> *** Posted from RemarQ - http://www.remarq.com - Discussions Start Here
(tm) ***
>
Received on Mon Dec 21 1998 - 07:59:49 CST