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:

How to add a datafile to a bigfile tablespace in Oracle 23ai Database.

  Syntax for Bigfile Tablespace: SQL > ALTER TABLESPACE tablespace_name ADD DATAFILE SIZE 10G AUTOEXTEND ON NEXT 1G MAXSIZE UNLIMITED; Ho...