Re: Full table scan despite appropriate indexes

On Feb 7, 5:09 pm, "aravind.kanda_at_gmail.com" <aravind.ka..._at_gmail.com> wrote:
> Nope, it did not help. I corrected the syntax as per your suggestion.

Aravind,

Actually I made two suggestions, correcting the syntax of your query that used TWO index hints, and then to run the query with JUST the one hint to use the index that matters (index COVSUMM_MONTHSK ON MISP.TCOVERAGES_SUMMARY)

What happened w/ the single-hint query?

Actually - silly me ... I just reviewed your query. You do not have the hint comment block immediatley following the SELECT. Move it to immediately follow the SELECT. That is the only place Oracle will recognize it.

If it STILL won't use it, back off and eliminate the join and just do a simple select on TCOVERAGES_SUMMARY for a specific value of MONTH_SK you know to exist. EXPLAIN PLAN and see if the index is used. If not, issue the hint

   select /*+ index(t covsumm_monthsk) */

          *
   from tcoverages_summary t
   where month_sk = <some value>
   ;

If you get desperate, another approach is to use ORDERED to force the join order, to drive the query on your indexed table.

select /*+ ORDERED */

       sum(mtd_written_prm_am)
from "MISP"."TCOVERAGES_SUMMARY" T5,

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

If Oracle does not go ahead and use the index for the above then I don't know what to tell you.

Hope that helps,
JH Received on Wed Feb 07 2007 - 19:17:54 CST