Re: Full table scan despite appropriate indexes

I am using 9i.

This is the actualy query I am using:

select sum(mtd_written_prm_am) /*+ index(t5 covsumm_monthsk, t1 idx_tdate_year_month_ct) */
from "MISP"."TCOVERAGES_SUMMARY" T5,

        "MISP"."TDATE" T1
where (T5."MONTH_SK" = T1."DATE_SK")
and (T1."YEAR_MONTH_CT" = 200312)

The explain plan is below:

Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop

SELECT STATEMENT Optimizer Mode=CHOOSE		1  	 	6939
  SORT AGGREGATE		1  	12
    HASH JOIN		8 M	94 M	6939
      TABLE ACCESS BY INDEX ROWID	MISP.TDATE	30  	210  	2
        INDEX RANGE SCAN	MISP.IDX_TDATE_YEAR_MONTH_CT	30  	 	1
      TABLE ACCESS FULL	MISP.TCOVERAGES_SUMMARY	11 M	57 M	6911

There are 11.9M records in tcoverages_summary. 12K was a typo. Actually there are 19K records that satisfy the following condition:

select count(*)
from "MISP"."TCOVERAGES_SUMMARY" T5
where month_sk in (select date_sk from "MISP"."TDATE" T1 where T1."YEAR_MONTH_CT" = 200312) The real purpose behind this is to optimize the query by adding appropriate indexes. In this case Oracle seems to be doing a full table scan unnecessarily. I am forcing index to use hints for only testing purposes, since it does not pick the existing index - MONTH_SK on TCOVERAGES_SUMMARY and DATE_SK & YEAR_MONTH_CT on TDATE).

Thanks for your help. Received on Wed Feb 07 2007 - 14:40:47 CST