You may want to rebuild an index when index entries are removed as a result of many rows being deleted from the table.

After using the command "analyze index index_name validate structure" you can view the information stored in the session-specific INDEX_STATS dictionary view to decide if you need to rebuild the index.

If the length of all deleted values (stored in INDEX_STATS.DEL_LF_ROWS_LEN) is more than 20% of the the sum in bytes of the length of all values (stored in INDEX_STATS.LF_ROWS_LEN), then it might be good idea to rebuild the index.

The following script I made, accepts as parameters the schema name(s) and index name(s) and rebuilds all indexes which need to be rebuilt.

accept owner prompt "Schema name for Indexes_to_rebuild (Enter for all): "
accept index prompt "Index name to validate (Enter for all indexes owned by &owner): "
--
set serveroutput on
set verify off
prompt
--
DECLARE
--
w_owner varchar2(30); w_index varchar2(30);
sql_text varchar2(256); sql_text2 varchar2(256);
Dynamic_Cursor integer; Dynamic_Cursor2 integer;
dummy integer; dummy2 integer;
index_usage pls_integer;
w_lf_rows_len index_stats.lf_rows_len%TYPE;
w_broi pls_integer := 0;
w_red varchar2(300);
--
cursor validate is
select
OWNER, OBJECT_NAME
from
SYS.DBA_OBJECTS
where
OWNER like upper('%&owner%') and
OBJECT_NAME like upper('%&index%') and
OBJECT_TYPE = 'INDEX'
order by
OBJECT_NAME;
--
BEGIN
--
dbms_output.enable(60000);
open validate;
fetch validate into w_owner, w_index;
while validate%FOUND loop
Dynamic_Cursor := dbms_sql.open_cursor;
sql_text := 'analyze index '||w_owner||'.'||w_index||' validate structure';
dbms_sql.parse(Dynamic_Cursor, sql_text, dbms_sql.native);
dummy := dbms_sql.execute(Dynamic_Cursor);
dbms_sql.close_cursor(Dynamic_Cursor);
-- let us see now if we have to rebuild this index:
begin
select LF_ROWS_LEN into W_LF_ROWS_LEN from index_stats;
select decode(W_LF_ROWS_LEN,0,19,(DEL_LF_ROWS_LEN/LF_ROWS_LEN)*100)
into index_usage
from index_stats;
select name||' '||to_char(ceil((blocks*8)/1024)) into w_red from index_stats;
-- if index_usage > 20, then we have to rebuild the index
-- you may replace 20 with 30 if you wish
if index_usage >= 20 then
begin
Dynamic_Cursor2 := dbms_sql.open_cursor;
sql_text2 := 'alter index '||w_owner||'.'||w_index||' rebuild';
dbms_sql.parse(Dynamic_Cursor2, sql_text2, dbms_sql.native);
dummy2 := dbms_sql.execute(Dynamic_Cursor2);
dbms_sql.close_cursor(Dynamic_Cursor2);
end;
-- message to the DBA:
dbms_output.put_line(w_owner||'.'||w_index||' rebuilt for index usage being '||index_usage);
w_broi := w_broi+1;
end if;
end;
fetch validate into w_owner, w_index;
end loop;
close validate;
if w_broi = 0
then dbms_output.put_line(CHR(10)||'No indexes need not to be rebuilt (perhaps)'||CHR(10));
elsif w_broi = 1
then dbms_output.put_line(CHR(10)||'Only 1 index had to be rebuilt');
else
dbms_output.put_line(CHR(10)||w_broi||' indexes had to be rebuilt');
end if;
--
END;
/