Re: slow query - index usage question

"Howard J. Rogers" <howardjr2000_at_yahoo.com.au> wrote in message news:3m3ea.3760$dE2.9223_at_newsfeeds.bigpond.com...
> > >Furthermore: it has been said often (by such knowledgeable people as
> > >T.Kyte) that a hint is a hint not an order. A hint adjusts factors
> > >that are taken into the calculation of the costs. So depending on the
> > >information the optimizer has, it still can (and will) decide that it's
> > >more cheaper to use a full table scan compared to an index fast full
> scan.
>
> I haven't been following this thread, but this particular bit is simply
> wrong. "Knowledgeable people", including Jonathan Lewis in this very group
> not more than about 6 weeks ago, have quite clearly stated that a hint is
> indeed an order and will not be ignored by the Optimizer, unless it is
> flatly invalid (directing the use of an invalid index, for example).
>
> They are directives, not suggestions.
>
> Regards
> HJR
And having just read the AskTom article that a further post in this thread made reference to, I'm going to say for what must be the first time that Tom is wrong. That article contains the statement that 'hints are suggestions not directives'... but that just isn't so.

Because this one could run and run, let me just paste in Jonathan's post of 3rd October 2002 (so it was a bit more than 6 weeks ago! Sorry!!):

Quote on:

"Hints" are directives.

There are bugs around., and there are plenty of reasons why the hints given are deemed to be irrelevant (the smarter the CBO gets, the more precise your hinting has to be).

A hint basically says:

    IF you are about to take action X
    then the only option you have is Y

This can be confirmed by reviewing
10053 trace files.

However, Oracle is often capable of avoiding action X and therefore appears to have ignored the hint.

Quote Off.

Regards
HJR Received on Wed Mar 19 2003 - 13:37:25 CST