Full table scan despite appropriate indexes

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 This two tables are part of a star schema. I have an index on the sales_summary table on the month_key column. However, 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? It doesnt work even if I add the index as an hint. Stats are up to date.

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.

Pl. help.

Arvind. Received on Wed Feb 07 2007 - 12:10:29 CST