Re: Cost based optimizer

In article <E0vpF3.K83_at_boss.cs.ohiou.edu> austby_at_centuryinter.net (A Ustby) writes:
>I just got back from OOW and was reading a paper titled "Tuning Oracle SQL"
>(paper #171).
>
>It talked about adding "hints" to SQL statements that were using the Cost
>based optimizer.
>
>I thought the whole reason for using a cost based optimizer vs rules based was
>that the optimizer was suppose to be smart and determine the best path to get
>to the data (and thus making the coder of the SQL not have to know the
>physical DB design - so that when changes are made to the physical structure,
>you don't have to go out and change code "hints" or special ordering in the
>where clause).

The cost based optimizer can easily outsmart itself, since it is so dependent on statistics, which may be skewed for a particular situation. You may know that the stats gathered are bogus, say, after a purge, or when they are first being gathered. Or, you might just disagree with the solution the optimizer comes up with, or simply know the best way to handle a situation, regardless of what the optimizer comes up with. People still can be smarter than computers.

>
>So if you add hints to SQL statements that use cost based optimization (and
>collect statistics), what is the advantage of cost based over rules based
>optimizer ????????????
>
>Thanks to anyone who can give me insight on this subject.
>
>Art
>

-- 
Joel Garry               joelga_at_rossinc.com               Compuserve 70661,1534
These are my opinions, not necessarily those of Ross Systems, Inc.   <> <>
%DCL-W-SOFTONEDGEDONTPUSH, Software On Edge - Don't Push.            \ V /
panic: ifree: freeing free inodes...                                   O