Does anyone know a way of detecting index stagnation through SQLPLUS. What are the relevant views/code to establish which indexes need rebuilding ?
Visit http://www.dbascripts.com, Here U will all sorst of sql queries U need.
There Nothing You cannot Do, The problem is HOW.
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
Thanks. I thought one could get away with not having to analyze each index at a time.
Click Here to Expand Forum to Full Width