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/