Re: rebuild or not rebuild index file, when ?, how?

From: andrew_webby at hotmail <spam_at_no.thanks.com>
Date: Wed, 11 Apr 2001 15:10:23 +0100
Message-ID: <986998232.9755.0.nnrp-09.c30bdde2@news.demon.co.uk>

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;
/
"PIERRE HIRTH" <p.hirth_at_sbm.mc> wrote in message news:9au52t$k4$1_at_front7m.grolier.fr...
> Hi,
>
> With an Oracle DataBase is it necessary to rebuild files index from time
 to
> time and how ?
>
> Thank you
>
>
>
Received on Wed Apr 11 2001 - 09:10:23 CDT