On Wed, Apr 17, 2002 at 04:28:03PM -0400, mlw wrote:
> Oracle has a cost based optimizer, and they allow you to override
> it, offer hints as to what it should do, or use the rules based
> optimizer. They know that a cost based optimizer can not generate
> the best query all the time.
Oracle's the wrong competition to cite here. IBM's optimiser and
planner in DB2 is rather difficult to override; IBM actively
discourages doing so. That's because it's the best there is. It's
_far_ better than Oracle's, and has ever been so. It just about
_always_ gets it right. Without presuming to speak for him, I'd
suggest that Tom probably wants to get the planner to that level,
rather than adding band-aids.
> I say it is obvious it can never know enough, since statistics are
Enough for what? The idea is that the statistics will get you the
best-bet plan. You're trying to redefine what the best bet is; and
Tom and others have suggested that a simple rule of thumb, "All else
being more or less equal, prefer an index," is not a good one.
> Now, given the choice of the two strategies on a table, both pretty
> close to one another, the risk of poor performance for using the
> index scan is minimal based on the statistics, but the risk of poor
> performance for using the sequential scan is quite high on a large
> table.
I thought that's what the various cost estimates were there to cover.
If this is all you're saying, then the feature is already there.
--
----
Andrew Sullivan 87 Mowat Avenue
Liberty RMS Toronto, Ontario Canada
<andrew@libertyrms.info> M6K 3E3 +1 416 646 3304
x110
