Index stagnation

[Sureshy]

Hi Friends.

Does anyone know a way of detecting index stagnation through SQLPLUS. What are the relevant views/code to establish which indexes need rebuilding ?

Thanks

Suresh

[sudip]

Hi!
Visit http://www.dbascripts.com, Here U will all sorst of sql queries U need.

[hacketta]

Normally I would issue the following command:

analyze index owner.index_name validate structure;

Then I'd run the query:

select del_lf_rows * 100 / decode(lf_rows,0,1,lf_rows) from index_stats
where name = 'index_ name'

If 20%+ of rows are deleted then the index should be rebuilt.

Bear in mind that the index_stats table can only hold one row at a time so you must analyze and query each index at a time, but of course this can be automated.

Hope this helps

[Sureshy]

Thanks Folks

Much Appreciated.

Suresh

[joyce_ank]

Thanks. I thought one could get away with not having to analyze each index at a time.