Monday, May 11, 2015

Index (when and how to rebuild it)

Index should rebuild if blevel column in dba_indexes greater than 3 or you can query INDEX_STATS for lef_rows and del_lef_rows to know the percentage of deleted rows from the index. if the percentage is more than 20% but make sure to analyze index before check blevel column
using command
SQL> analyze index OWNER.INDEX_NAME validate structure;

Now check index from dba_indexes.

SQL> select blevel ,owner,index_name from dba_indexes where index_name='<INDEX_NAME>'
SQL>select height,lf_rows,del_lf_rows,(del_lf_rows/lf_rows)*100 as Percent_del_rows from index_stats;
Note that index_stats carry only one index and should execute this query after analyze index and in the same session as all info
recorded in your PGA.

Below script will help you to get all index that need rebuild for specific schema but first but your schema name in where condition.

  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 = '<Schema_Owner>';
  /* Define maximums. This section can be customized. */
  vMaxHeight := 3;
  vMaxDel    := 20;

  /* For every index, validate structure */
  OPEN cGetIdx;
     FETCH cGetIdx INTO vOwner,vIdxName;
     /* Open DBMS_SQL cursor */
     vCursor := DBMS_SQL.OPEN_CURSOR;
     /* Set up dynamic string to validate structure */
     vAnalyze := 'ANALYZE INDEX ' || vOwner || '.' || vIdxName || ' VALIDATE STRUCTURE';
     vNumRows := DBMS_SQL.EXECUTE(vCursor);
     /* Close DBMS_SQL cursor */
     /* Does index need rebuilding?  */
     /* If so, then generate command */
     SELECT height,lf_rows,del_lf_rows INTO vHeight,vLfRows,vDLfRows
     IF vDLfRows = 0 THEN         /* handle case where div by zero */
        vDLfPerc := 0;
        vDLfPerc := (vDLfRows / vLfRows) * 100;
     END IF;
     IF (vHeight > vMaxHeight) OR (vDLfPerc > vMaxDel) THEN
        DBMS_OUTPUT.PUT_LINE('ALTER INDEX ' || vOwner || '.' || vIdxName || ' REBUILD;');
     END IF;

  CLOSE cGetIdx;

1 comment: