Tip

Which indexes require a re-build?

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:

DMOHAR.TEMP_INDEX_DATA_MED(V_ID number, 
                            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 
DECLARE 
   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%'; 



BEGIN 
   DBMS_OUTPUT.ENABLE(1000000); 
   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'); 
   DBMS_OUTPUT.PUT_LINE((v_time1)); 
   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); 
      DBMS_SQL.CLOSE_CURSOR(v_cursor); 
      /*** 
      IF v_count = 0 THEN 
          DBMS_OUTPUT.PUT_LINE('command executed'); 
      ELSE 
          DBMS_OUTPUT.PUT_LINE('NOT executed'); 
      END IF; 
      ***/ 
      SELECT LF_ROWS,DEL_LF_ROWS 
        INTO v_lf_count,v_del_lf_count 
        FROM index_stats;  

      IF v_lf_count >

    Requires Free Membership to View

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; SELECT SYSDATE 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); INSERT INTO DMOHAR.TEMP_INDEX_DATA_MED VALUES (93932211, RPAD(to_char(v_run_date,'yyyymmdd hh24:mi')||','|| v_owner||'.'||v_index_name||','|| v_lf_count||','|| v_del_lf_count||','|| v_del_lf_percentage,100) ); COMMIT; -- 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_SQL.CLOSE_CURSOR(v_cursor); DBMS_OUTPUT.PUT_LINE('Index '||v_index_name||' Re-built'); END IF; END IF; END LOOP; DBMS_OUTPUT.PUT_LINE(' SELECT LF_ROWS,DEL_LF_ROWS, (DEL_LF_ROWS'|| '/LF_ROWS)*100 FROM INDEX_STATS;'); v_time2 := DBMS_UTILITY.GET_TIME; DBMS_OUTPUT.PUT_LINE(to_char((v_time2-v_time1)/100) ||' seconds '); END; /

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:

Bill 
X-Bob (deleted entry) 
Mary 

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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

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:

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.