Bookshelf v7.5: Reorganize Indexes

When you initially create an index, the database server attempts to store the index entries in contiguous physical pages linked with pointers. This structure reflects the sequential nature of B-tree indexes and optimizes the sequential read operations the database server makes when it scans an index.

At some point, the additional read operations required to scan an index degrades query performance. In some cases, the increase in scan time can be dramatic. The remedy for this kind of fragmentation is index reorganization, an operation that compacts the index pages to minimize fragmentation and restores to a greater degree the sequential order of the physical pages.

Administrators always have the option to drop and recreate indexes. This two-statement operation allocates a new set of index pages which are roughly contiguous and sequential. All the database vendors also supply tools which the administrator can use to reorganize indexes.

Vendor Tools

The easiest way to reorganize an index is to simply drop and recreate the index using the DROP and CREATE INDEX commands. This manual operation two-step operation usually results in a clean index but can be inefficient, consume too many computer resources, and in some cases be impractical. Each of the database vendors has additional tools that reorganize indexes more efficiently.


Database	Tool	Description
DB2	REORG	Utility that rebuilds and compresses the physical structure of indexes. For additional information see DB2 Administration Guide V7.1, Volume 3: Performance.
Oracle 8i and 9i	ALTER INDEX ... REBUILD	SQL command that reorganizes an existing index to make it more compact. You can also use this command to change the index storage characteristics. This statement uses the existing index as the basis for the new one and is usually much faster than drop and create statements. For additional information, see Oracle9i Database Performance Guide and Reference.
SQL Server 2000	DBCC DBREINDEX DROP_EXISTING	Statement that can rebuild just a single specified index for a table or all of its indexes and takes advantage of optimizations not available with individual drop and create statements. Clause for the CREATE INDEX command that makes the reorganization more efficient than a simple drop and create statement. For additional information, see the Microsoft white paper RDBMS Performance Tuning Guide for Data Warehousing.

When to Reorganize

Index reorganization may help when the index's data is not be arranged efficiently. The index's data becomes scattered when the table is modified by insert or delete statements. You can gain a measure of index fragmentation by looking at trends in statistics generated for the indexes. Look for overflow pages, rapidly increasing page counts, increasing number of leaf pages, and an increase in the number of levels for B-tree indexes (NLEVELS in DB2 statistics).

Periodic Reorganizations

Periodically reorganizing indexes is important for query performance and is a task that should not be overlooked by the data warehouse administrator. You can set up and schedule jobs to automatically reorganize indexes using the tools provided by your database vendor.