Index Fragmentation / Rebuild

First analyze index
SQL>analyze index INDEX_NAME validate structure;Then query INDEX_STATS view1. 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 a10SQL> 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 coalesce2. index rebuildWhat is difference between coalesce and rebuild please go through below link for more detailshttp://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.