Re: Why are optimizer hints required?

I don't think it's documented - but I may be out of date. I have a few test cases with 10.1.0.1 that I haven't yet re-run on 10.1.0.3:

index i1 (n1, n2)

The index was used for a particular query if put in the hint:

    /*+index (t1 t1(n1)) */

but not if the hint was

    /*+index (t1 t1(n2, n1)) */

So it looked like:

    partial specification of correctly ordered leading edge

Looking at my hint, though, it could be that the comma is not supposed to be there. So when I re-install 10g, I'll run a more thorough set of tests.

-- 
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 March 9th 2005






"Niall Litchfield" <niall.litchfield_at_dial.pipex.com> wrote in message 
news:422f4e8e$0$10946$cc9e4d1f_at_news-text.dial.pipex.com...


> comments embedded

> "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message 

> news:d0mc6t$q0v$1_at_hercules.btinternet.com...

>>    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).

>

> This was what I was hinting at (sorry) with my earlier comment. I'm 

> interested if it means 'starting with col1,col2' or 'including both col1 

> and col2 with one of them as a leading column' or 'any usable index that 

> includes col1 and|or col2' - I'm sure this is all documented somewhere.

>

> In addition I'd be interested in any bugs/usage oddities (bitmap indexes) 

> etc that people have come across with this syntax.

>

> I guess what I'm saying is that the feature sounds very attractive, but I 

> wonder what its quirks are.

>

>

> -- 

> Niall Litchfield

> Oracle DBA

> http://www.niall.litchfield.dial.pipex.com

>