
rebuild or not rebuild index file, when ?, how?
I managed to find my previous posting on this and enclose a script for your
entertainment... ;-)
You must be connected as the user who owns the indexes, obviously. It takes
into account index 'height' and deleted rows as per others recommendations.
set serveroutput on
set pages 0
set feedback off
set echo off
DECLARE
height index_stats.height%TYPE;
del_lf_rows_len index_stats.del_lf_rows_len%TYPE;
lf_rows_len index_stats.lf_rows_len%TYPE;
del_perc number;
table_name user_indexes.index_name%TYPE;
sql_stmt varchar2(100);
dbname varchar2(20);
wday varchar2(11);
Begin
dbms_output.enable(50000);
select name into dbname from v$database;
select to_char(sysdate,'dd-Mon-yyyy') into wday from dual;
dbms_output.put_line('Index Analyze Report for '||dbname||' on
'||wday);
dbms_output.put_line('*****');
dbms_output.put_line('Analyzing indexes for '||user);
dbms_output.put_line('*****');
for t in (select index_name from user_indexes) loop
sql_stmt := 'analyze index '||t.index_name||' validate
structure';
execute immediate sql_stmt ;
execute immediate 'select height, del_lf_rows_len,
lf_rows_len from index_stats'
into height, del_lf_rows_len, lf_rows_len;
if (height > 3) then
dbms_output.put_line(t.index_name||' needs rebuilt
(height > 3)');
end if;
if (lf_rows_len != 0 and del_lf_rows_len != 0) then
del_perc := (del_lf_rows_len/lf_rows_len)*100;
if (del_perc > 20) then
dbms_output.put_line(t.index_name
||' needs rebuilt - high no. of
deleted entries ('
||round(del_perc,2)||'%)' );
end if;
end if;
end loop;
End;
/
Quote:
> Hi,
> With an Oracle DataBase is it necessary to rebuild files index from time
to
> time and how ?
> Thank you