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