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 updates and 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:

#!/bin/ksh

export ORACLE_HOME=/app/d006/01/product/ora/11204/bin
export ORACLE_SID=D006A
export FPATH=/tmp

$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
spool analyze_User1_indexes.tmp

select 'exec DBMS_STATS.UNLOCK_TABLE_STATS ('''|| user ||''','''|| table_name ||''');' from user_tables order by table_name asc;

begin
for x in ( select index_name from user_indexes where index_type = 'NORMAL')
loop
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
> 0)
and (height > 3 or ((del_lf_rows/lf_rows)*100) > 20);');
end loop;
end;
/

select 'exec DBMS_STATS.LOCK_TABLE_STATS ('''|| user ||''','''|| table_name ||''');' from user_tables order by table_name asc;

spool off
column name format a40
spool FPATH/analyze_User1_index_report.txt
PROMPT NAME | HEIGHT | LF_ROWS | DEL_LF_ROWS | RATIO (del_lf_rows/lf_rows) %
@@$FPATH/analyze_User1_indexes.tmp
spool off

EOF

exit

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 the application.

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 performance.

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 IDL.SQL script.

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.

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.

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 tables:

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 index nodes.

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

ALTER INDEX index_name REBUILD tablespace FLOP;

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
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:

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 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.

Update:

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."

This 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)