Re: Index Tuning Features

On Oct 11, 2006, at 3:00 PM, Andrew Sullivan wrote:

> On Wed, Oct 11, 2006 at 12:40:42PM -0400, Gregory Stark wrote:
>> poorly written query. In fact Oracle is going in the opposite
>> direction of
>> even relying on hints internally. Its plan stability feature
>> depends on
>> generating and storing hints internally associated with every query.
>
> But IBM, whose DB2 planner and optimiser is generally regarded as way
> better than Oracle's (at least by anyone I know who's used both),
> doesn't like hints. The IBM people all say the same thing Tom has
> said before: that the work to design the thing correctly is better
> spent making the planner and optimiser parts smarter and cheaper,
> because out of that work you also manage not to have the DBA
> accidentally mess things up by simple-minded rule-based hints. (Note
> that I'm not trying to wade into the actual argument; I'm just
> pointing out that even the biggest industry people don't agree on
> this point.)

DBAs can mess things up already if they misuse the tools they are
provided. Like 'rm'. Which is there, but should _RARELY_ be used on
database datafiles. The argument that people _could_ use them in a
bad way is silly. Of course, they could use them in a bad way,
that's not an _argument_. Everyone agrees people can be stupid.

However, the planner will never be perfect. I would like to see 1
out of every 500,000 queries actually benefit from a hint system
(which means that 499,999 of the queries were planned perfectly fine
by the planner). To fix my one query, that is crucially important to
my business, it is a much more sane approach to hint the system to
change its plan than it is to have to upgrade my binaries.

// Theo Schlossnagle
// CTO -- http://www.omniti.com/~jesus/
// OmniTI Computer Consulting, Inc. -- http://www.omniti.com/