When to Rebuild Indexes

How can you determine if an index needs to be dropped and rebuilt?

Expected answer: Run the ANALYZE INDEX command on the index to validate its structure and then calculate the ratio of LF_BLK_LEN/LF_BLK_LEN+BR_BLK_LEN and if it isn?t near 1.0 (i.e. greater than 0.7 or so) then the index should be rebuilt. Or if the ratio BR_BLK_LEN/ LF_BLK_LEN+BR_BLK_LEN is nearing 0.3.

 

analyze index i1 validate structure;
select lf_blks from index_stats;

 

 

One opinion:

index rebuilds are justified only if there are regular deletes and inserts of more than 20-30% of the records in the table.

 

select ‘analyze index ‘||owner||’.’||index_name||’ validate structure;’
from dba_indexes
where owner = ‘HR’;

 

CREATE OR REPLACE PROCEDURE analyze_index (owner IN VARCHAR2)
IS
   index_nam         VARCHAR2 (50);
   stmt              VARCHAR (100);
   index_ratio       NUMBER (3, 2);
   idx_rebuild_cnt   NUMBER        := 0;
   idx_valid_cnt     NUMBER        := 0;

   CURSOR dyn_cur
   IS
      SELECT index_name
        FROM dba_indexes
       WHERE owner = ‘HR’;
BEGIN
   FOR rec IN dyn_cur
   LOOP
      index_nam := rec.index_name;
      stmt :=
         ‘analyze index ‘ || owner || ‘.’ || index_nam
         || ‘ validate structure’;
      DBMS_OUTPUT.put_line (stmt);

      EXECUTE IMMEDIATE stmt;

      EXECUTE IMMEDIATE ‘insert into index_stats_log select * from index_stats’;

      SELECT lf_blk_len / (lf_blk_len + br_blk_len)
        INTO index_ratio
        FROM index_stats;

      IF (index_ratio < 0.7)
      THEN
         DBMS_OUTPUT.put_line (   ‘Index ratio of’
                               || index_nam
                               || ‘ is greater than 0.7: = ‘
                               || index_ratio
                              );
         idx_rebuild_cnt := idx_rebuild_cnt + 1;
      ELSE
         DBMS_OUTPUT.put_line (   ‘Index ratio of’
                               || index_nam
                               || ‘ is less than 0.7: = ‘
                               || index_ratio
                              );
         idx_valid_cnt := idx_valid_cnt + 1;
      END IF;
   END LOOP;

   DBMS_OUTPUT.put_line (‘Index Rebuild Count is ‘ || idx_rebuild_cnt);
   DBMS_OUTPUT.put_line (‘Index Without Rebuild Count is ‘ || idx_valid_cnt);
EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      NULL;
   WHEN OTHERS
   THEN
      — Consider logging the error and then re-raise
      RAISE;
END analyze_index;

 

select s.name,s.del_lf_rows*100/decode(s.lf_rows, 0, 1, s.lf_rows) pct_deleted, (s.lf_rows-s.distinct_keys)*100/ decode(s.lf_rows,0,1,s.lf_rows) distinctiveness, i.blevel blevel

from index_stats s, dba_indexes i

where i.index_name = s.name ;

 

One More Approach

– Find out indexes having height(blevel+1) > 4 i.e. Indexes having BLEVEL > 3
How:
SQL> select owner,index_name,table_name,blevel from dba_indexes where BLEVEL>3
– Analyze indexes and find out ratio of (DEL_LF_ROWS/LF_ROWS*100) is > 20
How:
First "Analyze the index with validate structure option" and then,

SQL> SELECT name,height,lf_rows,del_lf_rows,(del_lf_rows/lf_rows)*100 as ratio FROM INDEX_STATS;

 

 

Rebuilding Indexes

Avoids writing to redo logs and speeds up the index rebuild.

Alter index <index_name> rebuild unrecoverable;
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: