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
[code=oracle]
DECLARE
vOwner dba_indexes.own er%TYPE; /* Index Owner */
vIdxName dba_indexes.ind ex_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.hei ght%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_nam e
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%NOTFOUN D;
/* Open DBMS_SQL cursor */
vCursor := DBMS_SQL.OPEN_C URSOR;
/* Set up dynamic string to validate structure */
vAnalyze := 'ANALYZE INDEX ' || vOwner || '.' || vIdxName || ' VALIDATE STRUCTURE';
DBMS_SQL.PARSE( vCursor,vAnalyz e,DBMS_SQL.V7);
vNumRows := DBMS_SQL.EXECUT E(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;[/code]
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
[code=oracle]
DECLARE
vOwner dba_indexes.own er%TYPE; /* Index Owner */
vIdxName dba_indexes.ind ex_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.hei ght%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_nam e
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%NOTFOUN D;
/* Open DBMS_SQL cursor */
vCursor := DBMS_SQL.OPEN_C URSOR;
/* Set up dynamic string to validate structure */
vAnalyze := 'ANALYZE INDEX ' || vOwner || '.' || vIdxName || ' VALIDATE STRUCTURE';
DBMS_SQL.PARSE( vCursor,vAnalyz e,DBMS_SQL.V7);
vNumRows := DBMS_SQL.EXECUT E(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;[/code]