Issue identifying unused indexes in Oracle 9i

From: Roger Jackson <rjackson1_at_hotkey.net.au>
Date: Mon, 14 Jul 2003 20:06:09 +1000
Message-ID: <3f12809f_1@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. Received on Mon Jul 14 2003 - 05:06:09 CDT