Re: Full table scan despite appropriate indexes

<aravind.kanda_at_gmail.com> wrote in message news:1170901440.793885.41150_at_m58g2000cwm.googlegroups.com...
> John, last time I tried with two hints separately and with one hint
> only. It did not work.
>
> The ordered option did not work either. In fact it took longer.
>
> Now I tried with just one table as you said by hard coding the value
> for month_sk. It worked!
>
> Now what? I am totally lost. I still dont understand why the optimizer
> is not picking up the indexes.
>
>

Hints must be obeyed if legal, in context, and syntactically correct:

Since Oracle appears to be ignoring your hint to use a specific index in such a simple example, it would appear that your hint is either illegal or syntactically incorrect.

The obvious guess regarding legality is that the two columns involved in the joins are of different types, and a type-coercion is taking place which invalidates the use of index.

Always use dbms_xplan.display to review execution plans so that you can see the use of predicates.

-- 
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

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