RE: best way to identify an unused index

Chirag, the height of a normal Oracle index is pretty much a function of the number of entries in the index and generally does not have a direct bearing on the state of the index.=20

HTH -- Mark D Powell --

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Chirag DBA Sent: Thursday, May 12, 2005 3:23 PM
To: Paula_Stankus_at_doh.state.fl.us
Cc: arivenes_at_llnl.gov; dba.orcl_at_gmail.com; oracle-l_at_freelists.org Subject: Re: best way to identify an unused index

Unused means I am assuming here that the index which was working now due to getting splitted it is not working now.

You can go to index_stats view and check the value of column height, if that exceeds 4, it means yr index has got splitted. You need to rebuild index.

You can check explain_plan to see whether it is working or not?

If INDEX is not splitted, you need to generate statistics for CBO and at last you can use hint to make the index getting used in the query extensively.

Hope I m on the right way,

Regards - Chirag=3D20

On 5/12/05, Paula_Stankus_at_doh.state.fl.us <Paula_Stankus_at_doh.state.fl.us> w=3D
rote:
> In Oracle 9i what is the best way to identify an unused index?
> --
> http://www.freelists.org/webpage/oracle-l
>

--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l