By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,180 Members | 1,016 Online
Bytes IT Community
Submit an Article
Got Smarts?
Share your bits of IT knowledge by writing an article on Bytes.

Script to Rebuild the indexes that really needs to be rebuild .

debasisdas
Expert 5K+
P: 8,127
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
Expand|Select|Wrap|Line Numbers
  1. DECLARE
  2.   vOwner   dba_indexes.owner%TYPE;            /* Index Owner            */
  3.   vIdxName dba_indexes.index_name%TYPE;       /* Index Name             */
  4.   vAnalyze VARCHAR2(100);                     /* String of Analyze Stmt */
  5.   vCursor  NUMBER;                            /* DBMS_SQL cursor        */
  6.   vNumRows INTEGER;                           /* DBMS_SQL return rows   */
  7.   vHeight  index_stats.height%TYPE;           /* Height of index tree   */
  8.   vLfRows  index_stats.lf_rows%TYPE;          /* Index Leaf Rows        */
  9.   vDLfRows index_stats.del_lf_rows%TYPE;      /* Deleted Leaf Rows      */
  10.   vDLfPerc   NUMBER;                          /* Del lf Percentage      */
  11.   vMaxHeight NUMBER;                          /* Max tree height        */
  12.   vMaxDel    NUMBER;                          /* Max del lf percentage  */
  13.   CURSOR cGetIdx IS SELECT owner,index_name
  14.      FROM dba_indexes WHERE OWNER NOT LIKE 'SYS%';
  15. BEGIN
  16.   /* Define maximums. This section can be customized. */
  17.   vMaxHeight := 3;
  18.   vMaxDel    := 20;
  19.  
  20.   /* For every index, validate structure */
  21.   OPEN cGetIdx;
  22.   LOOP
  23.      FETCH cGetIdx INTO vOwner,vIdxName;
  24.      EXIT WHEN cGetIdx%NOTFOUND;
  25.      /* Open DBMS_SQL cursor */
  26.      vCursor := DBMS_SQL.OPEN_CURSOR;
  27.      /* Set up dynamic string to validate structure */
  28.      vAnalyze := 'ANALYZE INDEX ' || vOwner || '.' || vIdxName || ' VALIDATE STRUCTURE';
  29.      DBMS_SQL.PARSE(vCursor,vAnalyze,DBMS_SQL.V7);
  30.      vNumRows := DBMS_SQL.EXECUTE(vCursor);
  31.      /* Close DBMS_SQL cursor */
  32.      DBMS_SQL.CLOSE_CURSOR(vCursor);
  33.      /* Does index need rebuilding?  */
  34.      /* If so, then generate command */
  35.      SELECT height,lf_rows,del_lf_rows INTO vHeight,vLfRows,vDLfRows
  36.         FROM INDEX_STATS; 
  37.      IF vDLfRows = 0 THEN         /* handle case where div by zero */
  38.         vDLfPerc := 0;
  39.      ELSE
  40.         vDLfPerc := (vDLfRows / vLfRows) * 100;
  41.      END IF;
  42.      IF (vHeight > vMaxHeight) OR (vDLfPerc > vMaxDel) THEN 
  43.         DBMS_OUTPUT.PUT_LINE('ALTER INDEX ' || vOwner || '.' || vIdxName || ' REBUILD;');
  44.      END IF;
  45.  
  46.   END LOOP;
  47.   CLOSE cGetIdx;
  48. END;
Aug 1 '08 #1
Share this Article
Share on Google+