Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Full table scan despite appropriate indexes
<aravind.kanda_at_gmail.com> wrote in message
news:1170953824.159031.319000_at_m58g2000cwm.googlegroups.com...
> Finally I see some progress!!!
>
> Without hint: (explain plan use FTS)
>
> Predicate Information (identified by operation id):
>
> PLAN_TABLE_OUTPUT
> --------------------------------------------------------------------------------
> ---------------------------------------------------
>
> 2 - access("T2"."MONTH_SK"="T1"."DATE_SK")
> 4 - access("T1"."YEAR_MONTH_CT"=200312)
>
> With hint: (picked up the index)
>
> 5 - access("T1"."YEAR_MONTH_CT"=200312)
> 6 - access("T5"."MONTH_SK"="T1"."DATE_SK")
>
> Is it the order that matters here? What can I do avoid using hints?
> Why the optimizer is not picking the index when there is no hint?
>
So last time you posted the plan without the predicates, this time you post the predicates without the plan !
As I said several posts ago - Oracle thinks the join is going to return 8M rows. Since it estimates 30 rows from the small table, that means it expects about 265,000 per value in the large table. Since there are 12M rows in the large table there would appear to be about 45 distinct values for the month_sk as far as Oracle can see. If 45 is far from reality, then you have some sort of statistics problem with that column.
This, however, does not explain why your previous attempts at hinting failed. A typing error seems likely.
-- 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.htmlReceived on Thu Feb 08 2007 - 14:27:44 CST