First analyze index
SQL>analyze index INDEX_NAME validate structure;
Then query INDEX_STATS view
1. If del_lf_rows/lf_rows is > .2 then index should be rebuild.
2. If height is 4 then index should be rebuild.
3. If lf_rows is lower than lf_blks then index should be rebuild.
SQL> column status format a10
SQL> select trunc((del_lf_rows/lf_rows)*100,2)||'%' "status" from index_stats;
status
----------
21.83%
How to remove index fragmentation?
There are two way to remove fragmentation.
1. index coalesce
2. index rebuild
What is difference between coalesce and rebuild please go through below link for more details
http://download.oracle.com/docs/cd/B14117_01/server.101/b10739/indexes.htm#g1007548
SQL> alter index IDX_OBJ_ID coalesce;
SQL> alter index IDX_OBJ_ID rebuild;
SQL> alter index IDX_OBJ_ID rebuild online;
Note: If any DML statement is running on base table then we have to use ONLINE keyword with index rebuilding.
SQL> analyze index idx_obj_id validate structure;
Index analyzed.
SQL> select trunc((del_lf_rows/lf_rows)*100,2)||'%' "status" from index_stats;
status
-------
40.85%
SQL> alter index IDX_OBJ_ID rebuild online;
Index altered.
SQL> analyze index idx_obj_id validate structure;
Index analyzed.
SQL> select trunc((del_lf_rows/lf_rows)*100,2)||'%' "status" from index_stats;
status
--------
0%
Note: Index rebuild when index is fragmented or it is needed, otherwise index rebuilding is myth for improve performance.
9 comments:
I really like your site. Excellent content. Please continue posting such profound cotent.
thanks amigo! great post!.
I just added your web page to my favorites. I like reading your posts. Thanks!
I located your blog using google and I must say, this is among the best well written articles I have viewed in a long time.
This is a groovy short piece I especially loved it
Before I go, let me thank you for your patience with my English as (I am confident you have figured this at this time ,), English is not my principal language thus I am using Google Translate to build out how to jot down what I truly have in mind to write down .
Excellent post....Itz too helpful and easy to follow...thnx 4 d post
Excellent post....Itz too helpful and easy to follow...thnx 4 d post
Amazing! This blog looks just like my old one!
It's on a entirely different topic but it has pretty much the same layout and design. Superb choice of colors!
Feel free to visit my page: green coffee
Hi
Thanks for the explanation.
While I was applying this my test environment which is in 10.2.0.3 enterprise edition, it worked but the same is not working in my 11g R2 express edition.
No rows gets populated in index stats after analyzing one of the indexes.
Appreciate your help.
Regards
Roy
Post a Comment