rebuild or not rebuild index file, when ?, how? 
Author Message
 rebuild or not rebuild index file, when ?, how?
Hi,

With an Oracle DataBase is it necessary to rebuild  files index from time to
time and how ?

Thank you



Sat, 27 Sep 2003 13:15:36 GMT
 rebuild or not rebuild index file, when ?, how?

Pierre

This was covered in this NG recently. Please browse through the threads...


Quote:
> Hi,

> With an Oracle DataBase is it necessary to rebuild  files index from time
to
> time and how ?

> Thank you



Sat, 27 Sep 2003 18:12:42 GMT
 rebuild or not rebuild index file, when ?, how?
Hi,

Also, it's good to rebuild if your index has a high percentage of
deletions..depending on what book you read the percentage varies but I
recall seeing 10% and 20%.

To rebuild (the index in this example is  s_dept_id_pk), use

SQL> alter index s_dept_id_pk rebuild;

Index altered.

Steve

Quote:

> Every so often you should check the height of your indexes and see if
> the need rebuilding.  If your not having a performance problem than I
> would probably not do anything.  But if you need more I will give you
> a script to check the index(es) and tell which ones and how to rebuild
> them.


>> Hi,

>> With an Oracle DataBase is it necessary to rebuild  files index from
>> time to
>> time and how ?

>> Thank you



Sun, 28 Sep 2003 19:56:21 GMT
 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



Sun, 28 Sep 2003 22:10:23 GMT
 rebuild or not rebuild index file, when ?, how?
Please, could 'you tel me where the Output are printed
when using the dbms_output.put_line('....') function ?
And why i 'ahe the message insufiscient privilege' on line 6
when i execute this proc :

CREATE OR REPLACE PROCEDURE BO.CREER_F0902
AS
   i number;
   BEGIN
      i:=i+1;
execute immediate 'Create table bo.f0902 as select * from bo.v$f0902';
execute immediate 'drop table bo.f0902';

 EXCEPTION
 When NO_DATA_FOUND THEN
  dbms_output.put_line('toto');
END;
/

Thank's



Tue, 30 Sep 2003 00:00:45 GMT
 rebuild or not rebuild index file, when ?, how?
Do a 'set serveroutput on' first (run it from SQL/Plus) to see the output.
Otherwise you get nothing.

The reason it doesn't work is you don't have privileges (execute permission)
on dbms_output, so connect as SYS, and 'grant execute on dbms_output to
yourusername'. Then reconnect and try again.


Quote:
> Please, could 'you tel me where the Output are printed
> when using the dbms_output.put_line('....') function ?
> And why i 'ahe the message insufiscient privilege' on line 6
> when i execute this proc :

> CREATE OR REPLACE PROCEDURE BO.CREER_F0902
> AS
>    i number;
>    BEGIN
>       i:=i+1;
> execute immediate 'Create table bo.f0902 as select * from bo.v$f0902';
> execute immediate 'drop table bo.f0902';

>  EXCEPTION
>  When NO_DATA_FOUND THEN
>   dbms_output.put_line('toto');
> END;
> /

> Thank's



Sat, 04 Oct 2003 18:04:36 GMT
 
 [ 6 post ] 

 Relevant Pages 

1. Difference in ranking results from a full index rebuild verses an incremental rebuild

2. Rebuilding Master using the Rebuild Master Utility Tool (SQL Server 2000)

3. Long rebuild vs. Short Rebuild

4. Question about Index (Rebuild or not )

5. table requires rebuild - secondary index does not match ...

6. Use of TUtil32.dll for Pack and index rebuild of Paradox Files

7. How to rebuild Primary Index (PX) file?

8. rebuilding an index(s) to defragment the index

9. Rebuild DDF-File from .btr file

10. How to rebuild all indexes on database

11. Index rebuilding, possible?


 
Powered by phpBB® Forum Software