Re: Full table scan despite appropriate indexes

On Feb 7, 3:40pm, "aravind.kanda_at_gmail.com" <aravind.ka..._at_gmail.com> wrote:
> I am using 9i.

OK thx

> 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 ...

This is invalid hint syntax for /*+ INDEX(...) */ You can only give one table/alias per INDEX hint. See: http://download-east.oracle.com/docs/cd/B10501_01/server.920/a96533/hintsref.htm#5156

Probably you want something like

/*+ index(t5 covsumm_monthsk)

    index(t1 idx_tdate_year_month_ct)
*/

You're using the aliases T1 and T5 -- good. Are the index names exactly right?

Also: try giving JUST the hint to use the index on TCOVERAGES_SUMMARY.MONTH_SK and no other hints, leaving Oracle to sort out the rest. That will rule out any "contention" b/w the hints as to influence on the execution plan.

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

OK, 1% of rows - use of index looks like it should be a good idea. I'm wondering if Oracle "partially" obeys the invalid-syntax hint above and does something weird.

You can more detail as to what Oracle is doing, and why, by using a "10053 trace file." You do:

alter session set events '10053 trace name context forever, level 1'

The event no. 10053 occurs when SQL is parsed; above command cause Oracle to dump out optimizer stats when that occurs. This info is left in a trace file on your server in the configured "udump" dir.

Jonathan Lewis in the UK, who frequents this board, has a good example of a gnarly walk-through, involving hint debugging, which along the way explains a lot of the obscurities of these files: http://www.jlcomp.demon.co.uk/using_10053.html

You can also post the trace here, hairy as it looks and there are people who can make sense of it. But I'd rule out the obvious first: hint syntax, does the index exist, is it on the right column, does the index's name agree exactly w/ the hint. Heck, post the CREATE INDEX statement ;)

Regardless of the cost of using it, Oracle should follow what you do and use the hint. As before once you have it it will be obvious whether the index is faster than the full scan. BTW, how fast is the full scan (approximate, in seconds)?

HTH,
        JH Received on Wed Feb 07 2007 - 15:10:49 CST