In the following scenarios, one can think about rebuilding indexes->
1 If the ratio of deleted to actual leaf entries is more than 20% or
2 If the "blevel">3 (in table dba_indexes)
Here's how you can find it.You are supposed to do
analyze index validate structure ;
and then query the view INDEX_STATS
SELECT name,height,lf_rows,del_lf_rows,(del_lf_rows/lf_rows)*100 FROM INDEX_STATS;
Here's the command to rebuild indexes ->
alter index rebuild online;
You will require twice the space because during index rebuilding, the old index will still be there and will be dropped only when the new one has been created successfully.
References-> Oracle metalink document Id-182699.1 when and how to rebuild indexes. It provides the "bde_rebuild" script to automate rebuilding indexes .
The index creation can be made faster using the following->
alter index rebuild online parallel 8 nologging;
Change index to normal logging and noparallel->
alter index parallel 1 logging;
1 If the ratio of deleted to actual leaf entries is more than 20% or
2 If the "blevel">3 (in table dba_indexes)
Here's how you can find it.You are supposed to do
analyze index
and then query the view INDEX_STATS
SELECT name,height,lf_rows,del_lf_rows,(del_lf_rows/lf_rows)*100 FROM INDEX_STATS;
Here's the command to rebuild indexes ->
alter index
You will require twice the space because during index rebuilding, the old index will still be there and will be dropped only when the new one has been created successfully.
References-> Oracle metalink document Id-182699.1 when and how to rebuild indexes. It provides the "bde_rebuild" script to automate rebuilding indexes .
The index creation can be made faster using the following->
alter index
Change index to normal logging and noparallel->
alter index