Which indexes require a re-build?

This script will determine which indexes require a re-build to assist with system performance.

This script is used to determine which indexes require a re-build to assist with system performance. A ratio of

10-20% deleted lf rows to lf rows may be candidates. The script will attempt the used of dynamic SQL to execute the DDL commands required to execute the analyze table commands directly. Script will then re-build the indexes above a specified level of deleted rows. This script uses a table to store table/index modification details table format:

                            V_DATA  VARCHAR2(100) ) 

The script also requires access to the Package DBMS_UTILITY and must be run under SYS user.

set linesize 200 
set long 200 
set pagesize 1000 
set serveroutput on size 1000000 
   v_table_name    dba_tables.table_name%TYPE; 
   v_index_name    dba_indexes.index_name%TYPE; 
   v_owner         dba_indexes.owner%TYPE; 
   v_lf_count      number; 
   v_del_lf_count  number; 
   v_ratio         number; 
   v_cursor        INTEGER; 
   v_count         PLS_INTEGER; 
   v_del_lf_percentage NUMBER; 
   v_time1         NUMBER; 
   v_time2         NUMBER; 
   v_run_date      DATE; 

CURSOR get_indexes IS 
  SELECT index_name gi_index_name, 
         owner      gi_owner, 
         table_name gi_table_name 
    FROM dba_indexes 
   WHERE owner = 'CIS' 
     AND table_name not like 'C_RPTWORK%'  
     AND table_name like 'C_%'; 
     --AND table_name like 'C_SU%'; 

   DBMS_OUTPUT.PUT_LINE('Starting dynamic SQL to Analyze index structures'); 
   DBMS_OUTPUT.PUT_LINE('This MUST be run under SYS'); 
   v_time1 := DBMS_UTILITY.GET_TIME; 
   -- DBMS_OUTPUT.PUT_LINE(to_char(to_date(v_time1),'yyyymmdd hh24:mi'); 
   FOR cur_index IN get_indexes LOOP 
      v_owner      := cur_index.gi_owner; 
      v_index_name := cur_index.gi_index_name; 

      v_cursor := DBMS_SQL.OPEN_CURSOR; 
      DBMS_SQL.PARSE(v_cursor, 'ANALYZE INDEX '||v_owner||'.'||v_index_name|| 
                 ' VALIDATE STRUCTURE',dbms_sql.native); 
      v_count := DBMS_SQL.EXECUTE(v_cursor); 
      IF v_count = 0 THEN 
          DBMS_OUTPUT.PUT_LINE('command executed'); 
          DBMS_OUTPUT.PUT_LINE('NOT executed'); 
      END IF; 
        INTO v_lf_count,v_del_lf_count 
        FROM index_stats;  

      IF v_lf_count > 0 AND v_del_lf_count > 0 THEN 
          SELECT round((v_del_lf_count/v_lf_count)*100,4) 
            INTO v_del_lf_percentage 
            FROM index_stats; 

            INTO v_run_date 
            FROM DUAL; 

         IF v_del_lf_percentage > 10 THEN 
            DBMS_OUTPUT.PUT_LINE(to_char(v_run_date,'yyyymmdd hh24:mi') ||' '|| 
                        v_owner||'.'||v_index_name||' LF ROWS : '||v_lf_count|| 
                        '  Del lf rows: '|| 
                        v_del_lf_count||' percentage : '||v_del_lf_percentage); 
                           RPAD(to_char(v_run_date,'yyyymmdd hh24:mi')||','|| 
                                        v_del_lf_percentage,100)  ); 
            -- Now re-build the ineffecient indexes. 
            v_cursor := DBMS_SQL.OPEN_CURSOR; 
            DBMS_SQL.PARSE(v_cursor,'ALTER INDEX '||v_owner||'.'||v_index_name|| 
                           ' REBUILD',DBMS_SQL.NATIVE); 
            v_count := DBMS_SQL.EXECUTE(v_cursor); 
            DBMS_OUTPUT.PUT_LINE('Index '||v_index_name||' Re-built'); 
         END IF; 
      END IF; 

          '/LF_ROWS)*100 FROM INDEX_STATS;'); 
   v_time2 := DBMS_UTILITY.GET_TIME; 
   DBMS_OUTPUT.PUT_LINE(to_char((v_time2-v_time1)/100) ||' seconds '); 

Reader Feedback

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

This is what I read in a note from an Oracle Expert: I'm a big believer that indexes do not need to be rebuilt except in very particular cases. 99% of index rebuilds are a waste of time. Some people look at the number of deleted entries in the index and rebuild based on that (analyze index index_name validate structure and then query index_stats to see statistics on the index such as deleted entries and such). I say that might be misleading. If 50% of the entries are deleted (leaving "holes" in the index), should we rebuild? Maybe -- maybe not. depends on how the index is used and what data it indexes.

Consider this:

create table emp ( empno int, ename varchar2(25) ); 
create index emp_idx1 on emp(empno); 
create index emp_idx2 on emp(ename) 

Now, suppose empno is filled in via a sequence. It's always increasing and we never reuse a value. In our system, we actually delete rows when we fire someone (a real HR system would never do that).

So, emp_idx1 develops "holes" over time that will never be reused. It we find it to be X% "empty" over time, it might be a candidate for a rebuild. We'll never reuse that deleted space (unless we end up deleting all index entries on a block).

But, the index on ename -- that's a little more chaotic. Say we have employees:

Bill Bob Mary 

Say we fire Bob, now we have:

X-Bob (deleted entry) 

Should we rebuild an index like that? Probably NOT. The reason: we are going to hire Hillary (Bill Hillary Mary). Hillary will reuse Bobs slot. There is no reason to get rid of that slot only to have to rebuild it again later. If the data you are indexing has a good probability of reusing a slot like that, rebuilding can actually slow you down over time (it takes time to split a block with empty entries -- the chances we need to split are reduced. If you rebuild, they'll go up; if you unsplit everything, got rid of the space. Now we have to re-split again, every time you rebuild). I find most indexes hit a steady state -- if you rebuild them, they'll go back into that state over time. So rebuilding is somewhat self-defeating in that case.

Make sure to BENCHMARK yourself here. Before rebuilding, capture statistics, performance metrics, IO's, etc against that index. After rebuilding, compare the results. If you got nothing back -- no increased performance, decreased IO, etc -- don't rebuild it again later, it doesn't buy you anything.

For More Information

  • What do you think about this tip? E-mail the Editor at tdichiara@techtarget.com with your feedback.
  • The Best Oracle Web Links: tips, tutorials, scripts, and more.
  • Have an Oracle tip to offer your fellow DBA's and developers? The best tips submitted will receive a cool prize--submit your tip today!
  • Ask your technical Oracle questions--or help out your peers by answering them--in our live discussion forums.
  • Check out our Ask the Experts feature: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.

This was first published in January 2002

Dig deeper on Oracle and SQL



Enjoy the benefits of Pro+ membership, learn more and join.



Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: