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
Printable View
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
Hi!
Visit http://www.dbascripts.com, Here U will all sorst of sql queries U need.
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 Folks
Much Appreciated.
Suresh
Thanks. I thought one could get away with not having to analyze each index at a time.