Rebuild index

Author: Vincent Fenoll, DBA Oracle Montreal

-- ################################################
-- # Created: 2005/01/01
-- # Name: rebuild_indx.sql
-- ################################################
-- #
-- # Compatible: 7 8i 9i 10g 11g
-- #
-- ################################################

spool rebuild_indx.log

drop table v_perso;
create table v_perso (text varchar2(500), index_name varchar2(100));

prompt
ACCEPT spoolfile CHAR prompt 'Fichier de spool : ';
ACCEPT schema CHAR prompt 'Schema (% autorisé) : ';
prompt
prompt
prompt Il faut reconstruire un index quand :
prompt   - le nombre d entrees detruites represente 20% ou plus des entrees courantes
prompt   - la profondeur de l index est superieure a 3
prompt Les candidats possibles pour des index bitmaps :
prompt   - lorsque les donnees sont distinctes a plus de 99%
prompt
spool &spoolfile

set serveroutput on
set verify off
declare
 c_name        INTEGER;
 ignore        INTEGER;
 height        index_stats.height%TYPE := 0;
 lf_rows       index_stats.lf_rows%TYPE := 0;
 del_lf_rows   index_stats.del_lf_rows%TYPE := 0;
 distinct_keys index_stats.distinct_keys%TYPE := 0;
 cursor c_indx is
  select owner, table_name, index_name
  from dba_indexes
  where owner like upper('&schema')
    and owner not in ('SYS','SYSTEM');
begin
 dbms_output.enable (1000000);
 dbms_output.put_line ('Owner           Index Name                              % Deleted Entries Blevel Distinctiveness');
 dbms_output.put_line ('--------------- --------------------------------------- ----------------- ------ ---------------');

 c_name := DBMS_SQL.OPEN_CURSOR;
 for r_indx in c_indx loop
  DBMS_SQL.PARSE(c_name,'analyze index ' || r_indx.owner || '.' ||
                 r_indx.index_name || ' validate structure',DBMS_SQL.NATIVE);
  ignore := DBMS_SQL.EXECUTE(c_name);

  select HEIGHT, decode (LF_ROWS,0,1,LF_ROWS), DEL_LF_ROWS,
         decode (DISTINCT_KEYS,0,1,DISTINCT_KEYS)
         into height, lf_rows, del_lf_rows, distinct_keys
  from index_stats;
--
-- Index is considered as candidate for rebuild :
--   - when deleted entries represent 20% or more of the current entries
--   - when the index depth is more then 2 levels.(height starts counting from 1 so > 5)
-- Index is (possible) candidate for a bitmap index when :
--   - distinctiveness is more than 99%
--
-- VFL ajout de and (del_lf_rows/lf_rows) <> 1) pour enlever les indexes vides
  if ( height > 2 ) OR ( (del_lf_rows/lf_rows) > 0.2 and (del_lf_rows/lf_rows) <> 1) then
    dbms_output.put_line (rpad(r_indx.owner,16,' ') || rpad(r_indx.index_name,40,' ') ||
                          lpad(round((del_lf_rows/lf_rows)*100,3),17,' ') ||
                          lpad(height-1,7,' ') || lpad(round((lf_rows-distinct_keys)*100/lf_rows,3),16,' '));
    -- VFL
    insert into v_perso values ('ALTER INDEX '|| r_indx.owner || '.' || r_indx.index_name || ' REBUILD;', r_indx.index_name );
  end if;

 end loop;
 DBMS_SQL.CLOSE_CURSOR(c_name);
end;
/

select text from v_perso;
-- drop table v_perso;
spool off
set verify on