Re: Why are optimizer hints required?

From: Noons <wizofoz2k_at_yahoo.com.au>
Date: Wed, 09 Mar 2005 21:11:29 +1100
Message-ID: <422ecbce$0$31490$5a62ac22@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. Received on Wed Mar 09 2005 - 04:11:29 CST