Identifying which Oracle Indexes to Rebuild
Oracle Tips by Burleson
Executive Summary on index rebuilding
While it may be rare to rebuild an Oracle index
for performance reasons, there are some databases that will get
a measurable performance boost from rebuilding indexes.
These workloads have these characteristics:
High index fragmentation:
The SQL workload has lots of table DML causing lots of
deleted leaf blocks.
High index scan access
plans: The SQL workload is rich with index scans (index
fast-full scans and index range scans)
The rules for identification of candidates for index coalescing/rebuilding
depend on your specific index state. See MOSC notes
989186.1, 122008.1, 989093.1 for Oracle's suggestions on when to
coalesce/rebuild indexes. Also see my
updated notes on index coalesce or rebuilding and note this demonstration
of an index that benefits from
scheduled oracle index rebuilding.
Also, please see my
other notes on index rebuilding strategies, and my complete notes are found in
my book "Oracle
Tuning: The Definitive Reference".
Some people suggest that indexes require rebuilding when
deleted leaf rows appear or when the index has a suboptimal
number of block gets per access. While it is tempting to write a
script that rebuilds every index in the schema, bear in mind
that your schema may contain 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. Lets look at one method
for accomplishing this task.
An Oracle OTN member shared this Korn shell script to identify highly fragmented indexes, targeting indexes that are 4 levels or more, or indexes with a high number of deleted leaf blocks:
$ORACLE_HOME/sqlplus -s User1/pwd << EOF
set echo off
set termout off
set verify off
set trimspool on
set feedback off
set heading off
set lines 300
set pages 0
set serverout on
DBMS_STATS.UNLOCK_TABLE_STATS ('''|| user ||''','''|| table_name ||''');'
from user_tables order by table_name asc;
for x in ( select index_name from user_indexes
where index_type = 'NORMAL')
dbms_output.put_line('ANALYZE INDEX ' ||
x.index_name || ' COMPUTE STATISTICS;');
dbms_output.put_line('ANALYZE INDEX ' ||
x.index_name || ' VALIDATE STRUCTURE;');
dbms_output.put_line('select name, height, lf_rows,
del_lf_rows, round((del_lf_rows/lf_rows)*100,2) as ratio from index_stats
where (lf_rows > 100 and del_lf_rows
and (height > 3 or ((del_lf_rows/lf_rows)*100) >
DBMS_STATS.LOCK_TABLE_STATS ('''|| user ||''','''|| table_name ||''');' from
user_tables order by table_name asc;
column name format a40
PROMPT NAME | HEIGHT | LF_ROWS | DEL_LF_ROWS |
RATIO (del_lf_rows/lf_rows) %
How rare are "bad" indexes?
You cannot generalize to say that index
rebuilding for performance is rare, or even medium rare, it
depends on many factors, most importantly the characteristics of
In scientific applications (clinical, laboratory) where large datasets are
added and removed, the need to rebuild indexes is "common".
Conversely, in system that never update or delete rows, index rebuilding
rarely improves performance.
In systems that do batch DML jobs, index rebuilding "often" improves SQL
Oracle MOSC note
122008.1 has the officially authorized script to detect
indexes that benefit from rebuilding. This script detects
indexes for rebuilding using these rules:
Rebuild the index when
these conditions are true:
- deleted entries represent 20% or more of the current entries.
- the index depth is more then 4 levels.
When an index becomes skewed, parts of an index are
accessed more frequently than others. As a result of this skew, disk contention may
occur, creating a bottleneck in SQL performance. Hence, it is
important to periodically examine your indexes to determine if they have
become skewed and might need to be rebuilt.
Remember, 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 where that may be re-used if another
adjacent entry is required.
However, when large numbers of
adjacent rows are deleted, it is highly unlikely that Oracle
will have an opportunity to re-use the deleted leaf rows, and
these represent wasted space in the index. In addition to
wasting space, large volumes of deleted leaf nodes will make
index fast-full scans run for longer periods.
These deleted leaf nodes can be easily identified by running the
The number of deleted leaf rows
The term "deleted leaf node" refers to the number of index
inodes 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 allocate resources to
rebalance the index tree when rows are deleted.
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.
Oracle indexes can
support many millions of entries in three levels. Any Oracle
index that has spawned to a 4th level followed by a large delete
job might benefit from
rebuilding to restore the index to it's pristine state.
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.
Unfortunately, Oracle does not make it easy to capture this
information. In Oracle we must issue these commands to populate
the statistics in dba_indexes and related dictionary
ANALYZE INDEX index_name COMPUTE STATISTICS
ANALYZE INDEX index_name VALIDATE STRUCTURE
We might want to rebuild an index if the 'block gets' per access
is excessive. This happens when an index becomes "sparse"
after high delete activity, making full-index scans requires
unnecessary I/O. Another rebuild condition would be
cases where deleted leaf nodes comprise more than 20% of the
As you may know, you can easily rebuild an Oracle
index with the command:
ALTER INDEX index_name REBUILD
Done properly during scheduled
downtime, rebuilding an index is 100% safe. Note the use
of the tablespace option. When rebuilding multi-gigabyte
indexes, many DBA's will rebuild partitioned indexes into a
fresh, empty tablespace for greater manageability. ( I use the
convention ts_ndexname_flip, and ts_indexname_flop)
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
storage (initial new_initial next new_next freelists
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
Walk the existing index to get the index keys.
Populate temporary segments with the new tree
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 not rebuilt within the
default tablespace (usually SYS).
Be aware that it's always a good
idea to move an index into another tablespace and you must have
enough room in that tablespace to hold all of the temporary
segments required for the index rebuild, so most Oracle
administrators will double-size index tablespaces with enough
space for two full index trees.
When can we "prove" a benefit from an index
rebuild? Here, Robin Schumacher
proves that an index that is rebuilt in a larger tablespace will
contain more index entries be block, and have a flatter structure:
"As you can see, the amount of logical reads has
been reduced in half simply by using the new 16K tablespace and accompanying
16K data cache."
In an OracleWorld 2003 presentation titled 'Oracle Database 10g: The Self-Managing
Database' by Sushil Kumar of Oracle Corporation, Kumar
states that the new Automatic Maintenance Tasks (AMT) Oracle10g feature will
"automatically detect and re-build sub-optimal indexes."
Kim Floss article shows the Oracle 10g segment advisor recommending a
rebuild of an index:
"The page lists all the segments
(table, index, and so on) that constitute the object under review. The
default view ("View Segments Recommended to Shrink") lists any segments that
have free space you can reclaim."
Oracle index rebuild advisor (Source: Oracle Corporation)
See my related notes on index rebuilding: