Search This Blog

Monday, November 26, 2007

Index Fragmentation / Rebuild

How to find index is fragmented?

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:

Anonymous said...

I really like your site. Excellent content. Please continue posting such profound cotent.

Anonymous said...

thanks amigo! great post!.

Anonymous said...

I just added your web page to my favorites. I like reading your posts. Thanks!

Anonymous said...

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.

Anonymous said...

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 .

freemall said...

Excellent post....Itz too helpful and easy to follow...thnx 4 d post

freemall said...

Excellent post....Itz too helpful and easy to follow...thnx 4 d post

Anonymous said...

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

Anonymous said...

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