Oracle Index rebuild script

Index validate

Fast Index Rebuild.
Execute on DBMS_SQL must be granted from SYS


validate structure command obtains a dml enqueue lock on the table that the index


SQL> select distinct INDEX_TYPE from dba_indexes ;

INDEX_TYPE
---------------------------
IOT - TOP
LOB
FUNCTION-BASED NORMAL
FUNCTION-BASED DOMAIN
BITMAP
NORMAL
CLUSTER
DOMAIN





Set serveroutput on size 100000
DECLARE
vOwner dba_indexes.owner%TYPE; /* Index Owner */

vIdxName dba_indexes.index_name%TYPE; /* Index Name */

vAnalyze VARCHAR2(100); /* String of Analyze Stmt */

vCursor NUMBER; /* DBMS_SQL cursor */

vNumRows INTEGER; /* DBMS_SQL return rows */

vHeight index_stats.height%TYPE; /* Height of index tree */

vLfRows index_stats.lf_rows%TYPE; /* Index Leaf Rows */

vDLfRows index_stats.del_lf_rows%TYPE; /* Deleted Leaf Rows */

vDLfPerc NUMBER; /* Del lf Percentage */

vMaxHeight NUMBER; /* Max tree height */

vMaxDel NUMBER; /* Max del lf percentage */

CURSOR cGetIdx IS SELECT owner,index_name FROM dba_indexes WHERE OWNER NOT LIKE 'SYS%' and INDEX_TYPE='NORMAL' ; ------???????????

BEGIN

/* Define maximums. This section can be customized. */

vMaxHeight := 3 ;
vMaxDel := 20 ;

/* For every index, validate structure */

OPEN cGetIdx;

LOOP

FETCH cGetIdx INTO vOwner,vIdxName;

EXIT WHEN cGetIdx%NOTFOUND;

/* Open DBMS_SQL cursor */

vCursor := DBMS_SQL.OPEN_CURSOR;

/* Set up dynamic string to validate structure */

vAnalyze := 'ANALYZE INDEX ' || vOwner || '.' || vIdxName || ' VALIDATE STRUCTURE';

DBMS_SQL.PARSE(vCursor,vAnalyze,DBMS_SQL.NATIVE);

vNumRows := DBMS_SQL.EXECUTE(vCursor);

/* Close DBMS_SQL cursor */

DBMS_SQL.CLOSE_CURSOR(vCursor);

/* Does index need rebuilding? */
/* If so, then generate command */

SELECT height,lf_rows,del_lf_rows INTO vHeight,vLfRows,vDLfRows FROM INDEX_STATS;

IF vDLfRows = 0 THEN /* handle case where div by zero */
vDLfPerc := 0;
ELSE

vDLfPerc := (vDLfRows / vLfRows) * 100;

END IF;

IF (vHeight > vMaxHeight) OR (vDLfPerc > vMaxDel) THEN

DBMS_OUTPUT.PUT_LINE('ALTER INDEX ' || vOwner || '.' || vIdxName || ' REBUILD;');
END IF;

END LOOP;
CLOSE cGetIdx;
END;



==============

or

drop table
create table anuj_index_stats as select * from index_stats 1=2;
delete from anuj_index_stats;

BEGIN
FOR x IN (SELECT index_name FROM user_indexes WHERE index_type = 'NORMAL') LOOP

EXECUTE IMMEDIATE 'analyze index ' || x.index_name || ' validate structure';

EXECUTE IMMEDIATE 'insert into aux_index_stats select * from index_stats';

COMMIT;

END LOOP;
END;
/


=



validate structure command obtains a dml enqueue lock on the table and correspond index


First rule : If the index has height greater than 4 , rebuild the index

Second rule : { (del_lf_rows/lf_rows)*100 as ratio } if this ratio above 20% this
means high number of deletes or updates have occurred to the index
column.


SELECT name,height,lf_rows,del_lf_rows,(del_lf_rows/lf_rows)*100 as ratio
FROM INDEX_STATS;