Re: Issue identifying unused indexes in Oracle 9i

Hi!

Don't know whether this is expected behaviour, but it probably happens because access path is determined (and appropriate indexes selected) during parse phase, and explain plan requires parsing of the statement. So there's probably no mechanism (yet), which checks whether the statement is actually executed or not...

Tanel.

"Roger Jackson" <rjackson1_at_hotkey.net.au> wrote in message news:3f12809f_1_at_news.iprimus.com.au...
> 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.

>

>