BC remote Oracle DBA - Call (800) 766-1884  
Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation








Index Rebuilding Techniques

Oracle Tips by Burleson Consulting

As we know from Remote DBA 101 class, the goal of an index is to speed the process of finding data. An index file contains a data value for a specific field in a table and a pointer that identifies the record that contains a value for that field. In other words, an index on the last_name field for a table would contain a list of last names and pointers to specific records—just as an index to a book lists topics and page numbers to enable readers to access information quickly. When processing a request, the database optimizer will choose some or all of the available indexes to efficiently locate the requested rows.

IMPORTANT - See my updates notes on the issues surrounding index rebuilding.

Figure 10-22 illustrates some of the concepts of a B-tree index. The upper blocks contain index data that points to lower-level index blocks. The lowest-level blocks contain every indexed data value and a corresponding rowid used for locating the actual row of data.

Figure 10-100: A typical Oracle B-tree index

Normally, these indexes attempt to manage themselves internally to ensure fast access to the data rows. However, excessive activity within a table can cause Oracle indexes to dynamically reconfigure themselves. This reconfiguration involves three activities:

  • Index splitting This is when the addition of new table rows cause new index nodes to be created at existing levels (see Figure 10-23).

         Figure 10-101: Index splitting

  • Index spawning At some point, the Oracle indexes will reach the maximum capacity for the level and the Oracle index will spawn, creating a deeper level structure (see Figure 10-24).

         Figure 10-102: Oracle index spawning

  •   Index node deletion As you may know, 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.

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 schema, bear in mind that Oracle 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.

One vexing issue with Oracle indexes is that the information for an index rebuild must be gathered from two sources:

  •  The Oracle analyze index compute statistics command:


  • The Oracle analyze index validate structure command:


Once we gather information from these sources, we can generate a report with everything we need to know about the index internal structure:

                      # rep       dist.    # deleted              blk gets
Index                  keys        keys      leaf rows  Height      per access
--------------------  ------       -----     --------   ------      -----
CUST_IDX                   1     423,209       58,282        4          6
EDU_IDX_12               433      36,272        7,231        3          2
CUST_IDX                  12   1,262,393      726,361        4          6

From this report, 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 delete operations. 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 number 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 Oracle 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.

The script to make the report is complex because both the validate structure and compute statistics commands must be executed. The following code snippets are used to generate the report.

WARNING - This script for experts only.  It is extremely resource intensive.  DO NOT run it in an active production environment.


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;',
from Remote DBA_indexes
owner not in ('SYS','SYSTEM');
spool off;
set heading on;
set feedback on;
set echo on;


create table temp_stats as
name ,
most_repeated_key ,
distinct_keys ,
del_lf_rows ,
height ,
from index_stats;


insert into temp_stats
name ,
most_repeated_key ,
distinct_keys ,
del_lf_rows ,
height ,
from index_stats


Rem ind_fix.sql - Shows the details 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 '                      # rep       dist.    # deleted              blk s
prompt Index                  keys        keys      leaf rows  Height     per s
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
  height > 3
  del_lf_rows > 10
order by name;

spool off;

spool id6.sql;

select 'alter index '||owner||'.'||name||' rebuild tablespace '||tablespace_name||';'
from temp_stats, Remote DBA_indexes
  temp_stats.name = Remote DBA_indexes.index_name
  (height > 3
  del_lf_rows > 10);
select 'analyze index '||owner||'.'||name||' compute statistics;'
from temp_stats, Remote DBA_indexes
  temp_stats.name = Remote DBA_indexes.index_name
  (height > 3
  del_lf_rows > 10);

spool off;


select 'alter index '||owner||'.'||name||' rebuild tablespace '||tablespace_name
from temp_stats, Remote DBA_indexes
temp_stats.name = Remote DBA_indexes.index_name
(height > 3
del_lf_rows > 10);
select 'analyze index '||owner||'.'||name||' compute statistics;'
from temp_stats, Remote DBA_indexes
temp_stats.name = Remote DBA_indexes.index_name
(height > 3
del_lf_rows > 10);
spool off;

When to Rebuild Indexes

As you may know, you can easily rebuild an Oracle index with the following command:

ALTER INDEX index_name REBUILD TABLESPACE tablespace_name;

We might want to rebuild an index if the block gets per access is excessive, since excessive block gets indicate a fragmented B-tree structure. Another rebuild condition would be cases where deleted leaf nodes comprise more than 20 percent of the index nodes. Another reason to rebuild is when any index shows a depth of 4 or greater.

Automating Index Rebuilds

Now that we have identified the candidates for an index rebuild, we can run the following script during Oracle system downtime to rebuild all of the indexes.

Index rebuilding with the alter index rebuild is a very safe command. If anything goes wrong, Oracle aborts the operation and leaves the existing index in place. Many Remote DBAs regularly schedule index rebuilds during off-hours with no fear that a problem might occur. Here is a handy script for generating the alter index rebuild syntax:

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

select 'alter index ||owner||'.'||index_name||' rebuild tablespace '||tablespace_name||';'
from Remote DBA_indexes

spool off;

NOTE: It is interesting to note that Oracle indexing has changed dramatically between Oracle7 and Oracle8. While the functions of the indexes remain the same, the sizes of the indexes have been reduced by almost 50 percent.

As we stated, the alter index index_name rebuild command is a very safe way to rebuild indexes. Here is the syntax of the command:

alter index index_name
tablespace tablespace_name
storage (initial new_initial next new_next freelists new_freelist_number )

Unlike the old-fashioned method of dropping and re-creating the index, 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:

  • Walk the existing index to get the index keys.

  • Populate temporary segments with the new tree structure.

  • Once the operation has completed successfully, drop the old tree, and rename the temporary segments to the new index.

As you can see from the steps, you can rebuild indexes without worrying that you will accidentally lose the index. If the index cannot be rebuilt for any reason, Oracle will abort the operation and leave the existing index intact. Only after the entire index has been rebuilt does Oracle transfer the index to the new B-tree.

Most Oracle administrators run this script and then select the index that they would like to rebuild. Note that the TABLESPACE clause should always be used with the alter index rebuild command to ensure that the index is rebuilt in the same tablespace as the existing index. Be aware that you must have enough room in that tablespace to hold all of the temporary segments required for the index rebuild, so most Oracle Remote DBAs will double-size index tablespaces with enough space for two full index trees.

Also note that if you use the PARALLEL option to increase speed when rebuilding indexes, each parallel process will require enough space for the indexes’ initial extent size. Hence, if you rebuild indexes with PARALLEL DEGREE 4, you must have four times the space in the target tablespace.

Next, let’s take a look at the most important topic of this chapter—how to extend STATSPACK to capture table and index statistics.


This is an excerpt from "Oracle9i High Performance tuning with STATSPACK" by Oracle Press.

If you like Oracle tuning, you may enjoy the new book "Oracle Tuning: The Definitive Reference", over 900 pages of BC's favorite tuning tips & scripts. 

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.

Expert Remote DBA

BC is America's oldest and largest Remote DBA Oracle support provider.  Get real Remote DBA experts, call
BC Remote DBA today.




Remote DBA Service

Oracle Tuning Book


Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software







BC Remote Oracle Support

Remote DBA

Remote DBA Services

Copyright © 1996 -  2013 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.

Hit Counter