Re: Issue identifying unused indexes in Oracle 9i

Roger Jackson wrote:
>
> Hi,
>
> We have just upgraded some of development databases to Oracle 9i and we have
> started testing some of the new features. One feature in particular that we
> are testing is identifying unused indexes. After some testing with Oracle
> 9.2.0.3 on AIX 5.1 I have found that when performing an explain plan which
> contains an index (see example) it tells me that the index has been used
> even though I decide not to run the SQL statement. Is this expected
> behaviour????
>
> My interpretation of the documentation would suggest otherwise.
>
> eg. Query Plan
> ----------------------------------------------------------------------------
> ----------------------------------------------
> 0 DELETE STATEMENT CHOOSE Rows: 1 Cost: 1
> 1 DELETE RJACKSON.SHIPMENT Blocks: 1 of 4
> 2 UNIQUE INDEX UNIQUE SCAN RJACKSON.SHIPMENT_PK (PART_NO) [ANALYZED] Keys: 4
> RowsPerKey: 1 Rows: 1
>
> Has anybody seen other problems with this feature under Oracle 9iR2?
>
> Thanks in advance.
>
> Roger.

this is an efficiency mechanism. the idea is that by monitoring just the parse rather then every execute, then index monitoring is a low intrusion on the system

hth
connor

-- 
=========================
Connor McDonald
http://www.oracledba.co.uk

"Some days you're the pigeon, some days you're the statue"