Re: is index better option here

From: DA Morgan <damorgan_at_psoug.org>
Date: Wed, 13 Dec 2006 09:22:39 -0800
Message-ID: <1166030559.110929@bubbleator.drizzle.com>

Robert Klemme wrote:
> On 13.12.2006 09:14, peter wrote:

>> before index output of explain is
>>
>> SQL> @?\rdbms\admin\utlxplp.sql
>>
>> PLAN_TABLE_OUTPUT
>> --------------------------------------------------------------------
>>
>> --------------------------------------------------------------------
>> | Id  | Operation            |  Name       | Rows  | Bytes | Cost  |
>> --------------------------------------------------------------------
>> |   0 | SELECT STATEMENT     |             |     1 |    40 |     5 |
>> |*  1 |  TABLE ACCESS FULL   | N           |     1 |    40 |     5 |
>> |*  2 |   TABLE ACCESS FULL  | N           |     1 |    15 |     5 |
>> |*  3 |   TABLE ACCESS FULL  | N           |     1 |    15 |     5 |
>> --------------------------------------------------------------------
>> ********************************************************************
>>
>> after index output of explain is
>>
>> SQL> @?\rdbms\admin\utlxplp.sql
>>
>> PLAN_TABLE_OUTPUT
>> ---------------------------------------------------------------------------- 
>>
>>
>> ---------------------------------------------------------------------------- 
>>
>> | Id  | Operation                    |  Name       | Rows  | Bytes |
>> Cost  |
>> ---------------------------------------------------------------------------- 
>>
>> |   0 | SELECT STATEMENT             |             |     1 |    40 |
>>  5 |
>> |*  1 |  TABLE ACCESS FULL           | N           |     1 |    40 |
>>  5 |
>> |*  2 |   TABLE ACCESS BY INDEX ROWID| N           |     1 |    15 |
>> 27 |
>> |*  3 |    INDEX RANGE SCAN          | IND_N_CC    |   144 |       |
>>  1 |
>> |*  4 |   TABLE ACCESS BY INDEX ROWID| N           |     1 |    15 |
>> 27 |
>> |*  5 |    INDEX RANGE SCAN          | IND_N_CC    |   144 |       |
>>  1 |
>> ---------------------------------------------------------------------------- 
>>
>>

>
> Difficult to tell without more detail (DDL, Ora version etc.). The
> remaining table scan can be caused by any of these - and maybe more:
>
> - no statistics
>
> - statistics out of date
>
> - index missing
>
> - small table
>
> Btw, you usually get more information from the plan by using package
> DBMS_XPLAN.
>
> robert

utlxplp.sql contains the following in 10gR2:

select * from table(dbms_xplan.display());

So, it would seem, this is an older version of Oracle.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Received on Wed Dec 13 2006 - 11:22:39 CST