This code genenates a script to rebuld all the indexes with height greater than 3 for all the users except for SYS user. The script needs to be executed from SYS schema. This can also be executed from individual schemas by minor changes. If you need to exclude indxes of some other users that can also be added/changed in
cGetIdx cursor. To execute this code from any schema other than SYS. Execute on DBMS_SQL must be granted from SYS(not system) to the the specified user.
Indexes with height less than three (3) should not be rebuilded. If you want to go for indexes with less heights that can also be customized by changing the height parameter.
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%';
-
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.V7);
-
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;
