Note in-line
--
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:1110350653.144156.193000_at_g14g2000cwa.googlegroups.com...
> If it is too complex, then you can try to snapshot
> stats and reset them for the relevant objects as described in
> Jonathan's reply. Interestingly, this is a technique that Jonathan
> called "cheating" not so long ago. But it works and works good.
>
With a little luck, I called it "cheating" rather than cheating.
i.e. the quote marks are important.
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.
> If all else fails then there are ways of writing hints without
> explicit names for anything and yet still make them work.
> But you need to change how you write SQL. Mostly, you need to
> associate a table alias with every table in every SQL statement.
> Then you can use the alias to hint for index usage, rather than
> explicit index names. This will bypass SOME of the problems.
> But it is far from perfect.
>
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.
So it's worth knowing that in 10g, you can specify an index
by 'describing' it. e.g.
/*+ index(tab1 tab1(col1, col2)) */
Meaning 'use an index starting with (col1, col2).
Received on Wed Mar 09 2005 - 02:30:21 CST
