Re: Full table scan despite appropriate indexes

aravind.kanda_at_gmail.com wrote:
> With hint: (picked up the index)

aravind, glad you have at least one option now. (how much faster is it BTW?)

> Is it the order that matters here?

The order of tables in the FROM clause is relevent (in fact extremely important), in the presence of the ORDERED hint. See: http://download-east.oracle.com/docs/cd/B10501_01/server.920/a96533/hintsref.htm#5555 Also read up on USE_NL and INDEX since if you are actually going to use the hints J. Lewes suggested you should understand what they do.

> What can I do avoid using hints?

Basically everything possible, because hints increase maintenance and decrease flexibility and portability, even across Oracle upgrades. They are an act of last resort. So this means doing all things possible to get the optimizer work effectively: keep current statistics (database and system), have the appropriate model, indexes, etc., size your hardware appropriately, etc. It's good sign you are not comforatble w/ your situation using them.

> Why the optimizer is not picking the index when there is no hint?

That is really something you should try to figure out, because this particular query is not something Oracle should have trouble optimizing. You should not have to be telling Oracle explicitly to optimize a two-table join where the index is in place and where using it produces a stark contrast in speed. The best way to answer that is get your audience w/ the DBA ("god" ;) and recover the event trace file and peek in on what Oracle is deciding. Until then you can use hints as the band-aid they are there for.

Good luck! Received on Thu Feb 08 2007 - 13:38:25 CST