Re: Why are optimizer hints required?

Sorry,

Failed to state my intention precisely enough.

If you use just the index(tab_alias) hint, the optimizer will pick whichever index it thinks is the most appropriate index. This won't necessarily be the one you know is the best choice. In the case of the OP, the query does an FTS when unhinted: With just an index(tab_alias) the optimizer might still choose to ignore the index the OP wanted it to use and use an index that makes things even worse than an FTS.

The effect of just /*+ index(tab_alias) */ is also a little unstable as you upgrade. For example, it MIGHT make oracle choose the right index in 8i, but then choose the wrong index in 9i because the wrong index can be used with a skip_scan.

My pet comment on hints is that if you're going to use them at all, you should be as accurate as possible in describing EXACTLY what you want the optimizer to do - otherwise it may find a way to obey the hints and still do something appalling.

(Question: why does the spell-checker suggest

    "unhinged" for "unhinted"
is this Microsoft trying to tell me something about the Oracle optimizer).

-- 
Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/seminar.html
Public Appearances - schedule updated Jan 21st 2005






"Noons" <wizofoz2k_at_yahoo.com.au> wrote in message 
news:422ecbce$0$31490$5a62ac22_at_per-qv1-newsreader-01.iinet.net.au...


> Jonathan Lewis apparently said,on my timestamp of 9/03/2005 7:30 PM:

>

> >    I think I've also said "you need to tell the optimizer the

> >    truth, even if you have to lie to do so".  Apologies for

> >    the whimsy.

>

> Hehehe!   Touche!

> ;)

>

>

> >    The most significant nasty with index hints is that you

> >    have to name the index - and occasionally people decide

> >    to rename, or drop, or combine indexes so that the name

> >    in a hint no longer refers to a specific index.

>

> Akshally, beg to differ.  If you type the hint like this:

> /*+ INDEX(TAB1) */

> the CBO will pick the most suitable index for the columns

> used in your predicate (assuming some are indexed, of course)

> without you having to explicitly name the index.  At least in

> 8i and 9i.  Of course if you change the indexed columns to some

> not in your predicate, the hint will either be inactive or it

> will FTS anyway.

>

> >    by 'describing' it. e.g.

> >        /*+ index(tab1  tab1(col1, col2)) */

>

> THAT, is handy!  Thanks.