Rebuild Index


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.


No comments:

ORA-01552: cannot use system rollback segment for non-system tablespace 'TEMP'

 ORA-01552: cannot use system rollback segment for non-system tablespace "string" Cause: Used the system rollback segment for non...