Wednesday, August 29, 2012

INDEX REBUILD

ORACLE
When should you rebuild index and how?
Oracle's index rebuilding guidelines appear in recommends that indexes be periodically examined to see if they are candidates for an index rebuild:
“When an index is skewed, parts of an index are accessed more frequently than others. As a result, disk contention may occur, creating a bottleneck in performance.  It is important to periodically examine your indexes to determine if they have become skewed and might need to be rebuilt.”
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 its 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.

How to rebuild index?
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.

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.         Walk the existing index to get the index keys.
2.         Populate temporary segments with the new tree structure.
3.         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.

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


1 comment:

Anonymous said...

Copied directly from Burleson...