Re: Full table scan despite appropriate indexes

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Thu, 8 Feb 2007 16:26:57 -0000
Message-ID: <moudna6zXovN0lbYnZ2dnUVZ8qaqnZ2d@bt.com>

Notice that the TOAD plan table is not an up to date version of the plan table, so your output doesn't show the predicate information.

Can't you run SQL*Plus at your site ?

I don't know TOAD and have no call to use it, but I would guess you can do something like the following in an SQL window:

explain plan for
{your sql statement}

select * from table(dbms_xplan.display)

If you are lucky, the schema may have access to the correctly structured (default named) plan_table - which should give you the full plan.

If you're going to try hinting, then your initial attempt was incomplete. Try the following, and note that I've swapped the order of
the tables in your WHERE clause.

select

        /*+
            ordered
            use_nl(t5)
            index(t5 covsumm_monthsk)
        */

    sum(mtd_written_prm_am)
from

"MISP"."TDATE" T1,
"MISP"."TCOVERAGES_SUMMARY" T5

where

          (T5."MONTH_SK" = T1."DATE_SK") and (T1."YEAR_MONTH_CT" = 200312)
;

-- 
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html


<aravind.kanda_at_gmail.com> wrote in message 
news:1170948565.229859.135710_at_v33g2000cwv.googlegroups.com...
> Here is the output of dbms_xplan.display:
>
> PLAN_TABLE_OUTPUT
>
>
> ------------------------------------------------------------------------------------------
> | Id  | Operation                     |  Name                    |
> Rows  | Bytes | Cost  |
> ------------------------------------------------------------------------------------------
> |   0 | SELECT STATEMENT              |                          |
> 1 |    14 |  6939 |
> |   1 |  SORT AGGREGATE               |                          |
> 1 |    14 |       |
> |   2 |   HASH JOIN                   |                          |
> 8292K|   110M|  6939 |
> |   3 |    TABLE ACCESS BY INDEX ROWID| TDATE                    |
> 30 |   270 |     2 |
> |   4 |     INDEX RANGE SCAN          | IDX_TDATE_YEAR_MONTH_CT  |
> 30 |       |     1 |
> |   5 |    TABLE ACCESS FULL          | TCOVERAGES_SUMMARY       |
> 11M|    57M|  6911 |
> ------------------------------------------------------------------------------------------
>
> Note: cpu costing is off, TOAD_PLAN_TABLE' is old version
> 

Received on Thu Feb 08 2007 - 10:26:57 CST