Tuesday, June 30, 2009

Rebuild Index Online in oracle

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;