469,300 Members | 2,352 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Share your developer knowledge by writing an article on Bytes.

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

debasisdas
8,127 Expert 4TB
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
0 15816

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

10 posts views Thread by wackyphill | last post: by
reply views Thread by harlem98 | last post: by
1 post views Thread by Geralt96 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.