Identifying Which Indexes to Rebuild

Indexes require rebuilding when deleted leaf nodes appear or when the index has spawned into too many levels of depth. While it is tempting to write a script that rebuilds every index in the SAPR3 schema, bear in mind that SAP contains many thousands of indexes, and a complete rebuild can be very time consuming. Hence, we need to develop a method to identify those indexes that will get improved performance with a rebuild. Let’s look at a method for accomplishing this task. As I mentioned earlier in this chapter, Oracle index nodes are not physically deleted when table rows are deleted, nor are the entries removed from the index. Rather, Oracle “logically” deletes the index entry and leaves “dead” nodes in the index tree. These deleted leaf nodes can be easily identified by running the IDL.SQL script shown the output from the script is shown


# of  rep keys

  1. dist. keys

# deleted leaf rows


blk gets  per access



















In Listing we see several important statistics.   The number of deleted leaf nodes   The term “deleted leaf node” refers to the number of index nodes that have been logically deleted as a result of row deletes. Remember that Oracle leaves “dead” index nodes in the index when rows are deleted. This is done to speed up SQL deletes, since Oracle does not have to rebalance the index tree when rows are deleted. Index height   The height of the index refers to the number of levels that are spawned by the index as a result in row inserts. When a large amount of rows are added to a table, Oracle may spawn additional levels of an index to accommodate the new rows. Hence, an Oracle index may have four levels, but only in those areas of the index tree where the massive inserts have occurred. Oracle indexes can support many millions of entries in three levels, and any SAP index that has four or more levels would benefit from rebuilding. Gets per index access   The number of “gets” per access refers to the amount of logical I/O that is required to fetch a row with the index. As you may know, a logical “get” is not necessarily a physical I/O since much of the index may reside in the Oracle buffer cache. However, any SAP index with a number greater than 10 would probably benefit from an index rebuild. Unfortunately, Oracle does not make it easy to capture this information. In Oracle we must issue these commands:   ANALYZE INDEX index_name COMPUTE STATISTICS ANALYZE INDEX index_name VALIDATE STRUCTURE After you analyze the report above, you may want to consider rebuilding any index where the height is more than three levels, since three levels will support millions of index entries. Note that Oracle indexes will ìspawnî to a fourth level only in areas of the index where a massive insert has occurred, such that 99% of the index has three levels, but the index is reported as having four levels.   We might want to rebuild an index if the ìblock getsî per access is greater than five, since excessive ìblocks getsî indicate a fragmented b-tree structure. Another rebuild condition would be cases where deleted leaf nodes comprise more than 20% of the index nodes. As you may know, you can easily rebuild an Oracle index with the command:   ALTER INDEX index_name REBUILD   Finally, we may want to change a regular Oracle index to a bitmapped index. As you may know, bitmapped indexes are faster when the index key has less than 25 distinct values. If you have an index where the number of distinct keys is less than 25, you are on release 7.3.4 or above of Oracle, and you also get permission from SAP-AG, you are free to change index structures.   The SQL that created this report is shown in Listing revsp2t> cat id1.sql set pages 9999; set heading off; set feedback off; set echo off;

spool id4.sql; 

select ‘@id2.sql’ from dual; 

select ‘analyze index ‘||owner||’.’||index_name||’ validate structure;’, ‘@id3.sql;’ from dba_indexes where owner not in (‘SYS’,’SYSTEM’);

spool off; 

set heading on; set feedback on; set echo on;

@id4.sql @id5.sql

revsp2t> cat id2.sql create table temp_stats as select name , most_repeated_key , distinct_keys , del_lf_rows , height , blks_gets_per_access  from index_stats;

revsp2t> cat id3.sql insert into temp_stats (select name , most_repeated_key , distinct_keys , del_lf_rows , height , blks_gets_per_access  from index_stats ); 

id5.sql Rem ind_fix.sql – Shows the detals for index stats

set pagesize 60; set linesize 100; set echo off; set feedback off; set heading off;

column c1 format a18; column c2 format 9,999,999; column c3 format 9,999,999; column c4 format 999,999; column c5 format 99,999; column c6 format 9,999;

spool idx_report.lst; 

prompt prompt

prompt ‘

# rep

  1. dist.

# deleted


blk gets

prompt Index



leaf rows


per access

prompt ——————–






select distinct name c1, most_repeated_key c2, distinct_keys c3, del_lf_Rows c4, height c5, blks_gets_per_access c6 from temp_stats where height > 3 or del_lf_rows > 10 order by name;

spool off; 

spool id6.sql; 

select ‘alter index ‘||owner||’.’||name||’ rebuild tablespace ‘||tablespace_name ||’;’ from temp_stats, dba_indexes where = dba_indexes.index_name and (height > 3 or del_lf_rows > 10);v

select ‘analyze index ‘||owner||’.’||name||’ compute statistics;’ from temp_stats, dba_indexes where = dba_indexes.index_name and (height > 3 or del_lf_rows > 10);

spool off; 

Listing 5-12. The id1.sql and other scripts to produce a detailed index report

Now that we have identified the candidates for an index rebuild, we can run the following script during SAP system downtime to re-build all of the indexes (see Listing 5-13). 

Set heading off; Set pages 9999; Spool run_rebuild.sql;

select ‘alter index sapr3.’|| index_name|| ‘ rebuild tablespace ‘|| tablespace_name||’;’ from dba_indexes where owner = SAPR3.;

spool off; @run_rebuild Listing 5-13. A script to generate the index rebuild syntax Using ALTER INDEX REBUILD to Rebuild Indexes.

The ALTER INDEX index_name REBUILD command is very safe way to rebuild indexes. Here is the syntax of the command:   alter index index_name rebuild tablespace tablespace_name storage (initial new_initial next new_next freelists new_freelist_number ) Unlike the traditional method where we drop the index and recreate it, the REBUILD command does not require a full table scan of the table, and the subsequent sorting of the keys and rowids. Rather, the REBUILD command will perform the following steps: 

  1. 1. Walk the existing index to get the index keys. 
  2. 2. Populate temporary segments with the new tree structure. 
  3. 3. Once the operation has completed successfully, drop the old tree, and rename the temporary segments to the new index. 
To report this post you need to login first.

Be the first to leave a comment

You must be Logged on to comment or reply to a post.

Leave a Reply