Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Full table scan despite appropriate indexes

Re: Full table scan despite appropriate indexes

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Thu, 8 Feb 2007 20:27:44 -0000
Message-ID: <4LKdncqtb_MhGlbYnZ2dnUVZ8tGqnZ2d@bt.com>


<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.html
Received on Thu Feb 08 2007 - 14:27:44 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US