Re: Full table scan query

I don't have an 8.0.5 around anymore, but the comma between the table alias and the index name is optional, so there is (ought to be) no problem about having it or leaving it out.

It is actually documented somewhere that Oracle will attempt to obey an index hint - even if it is a very bad hint.

I don't recall if you tried the query with just:

    index(alias)
this should have made Oracle choose one of the available indexes, however stupid they may have been, provided at least one of them had at least one non-null column declaration.

The rebuild is consistent with Oracle not using the index unless hinted - but not with Oracle refusing to use the index completely. I was thinking more of the problem of a typo in the index or alias name, or the bug with the pl/sql parser needing an extra space after the '+' to interpret the hint correctly.

--
Jonathan Lewis
http://www.jlcomp.demon.co.uk

Now running 3-day intensive seminars
http://www.jlcomp.demon.co.uk/seminar.html

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

Author of:
Practical Oracle 8i: Building Efficient Databases


Norman Dunbar wrote in message ...


>Morning Jonathan,

>

>The hint was correct - I didn't fall into the old problem of having a

>comma between the alias and index name. The hint was indeed ignored.

>

>As for the optimizer ignoring the hint, I think it could be down to

>index statistics, or similar, but I'm still a little puzzled as to why,

>for reasone that will become clear.

>