Re: Full table scan despite appropriate indexes

On Feb 7, 1:10 pm, "aravind.kanda_at_gmail.com" <aravind.kanda_at_gmail.com> wrote:
> Hi all,
>
> I have two tables using simple inner join. For example,
>
> SELECT SUM(ACTUAL_AM) FROM SALES_SUMMARY SS, CALENDAR_TABLE CT WHERE
> SS.MONTH_KEY = CT.CALENDAR_KEY AND CT.YEARMONTH = 200312
>
> when i see the
> explain plan it seem to be going for a full table scan on the
> sales_summary table. How can I avoid this fts?

Please post

• Oracle version (so we know what optimizer features you have)
• the details of the EXPLAIN plan to which you refer
• the full SQL (if not what is above)

 > It doesnt work even if I add the index as an hint. Stats are up  > to date.

This part does not make sense. If the index is there and can be used, Oracle should be able to be compelled to use it. Maybe you have syntax issues. Can you post the version w/ the hint?

 > There are 12M records in sales_summary table of which only 12K is
 > returned using the above condition. So there is no reason (in my
 > mind that is) to think that a full table scan is faster than
 > index scan.

Since you're aggregating w/ SUM(), then sure, you'll get a tiny amount like 12K *output*, but the relevant factor for Oracle to decide on the index vs. full scan is how many detail *rows* it thinks will satsify the month=200312 filter. That is probably well more than 12K. But if it's small fraction then, yes it should use the index. How many rows in SALES_SUMMARY? How many have MONTH_KEY corresponding to 200312?

Really all you need to know is: will using the index be faster? Best way to decide is make Oracle use it (w/ hint) and compare. So the real problem is getting Oracle to obey your hint. After that, the answer to the question as to why Oracle wants to full-scan (e.g., because it predicts, perhaps correctly perhaps not, that it will be faster) will present itself.

HTH,
JH Received on Wed Feb 07 2007 - 14:12:02 CST