Bookshelf Home | Contents | Index | Search | PDF |
Siebel Analytics Performance Tuning Guide > The Database Servers > Periodic Tuning Tasks >
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.
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.
Bookshelf Home | Contents | Index | Search | PDF |
Siebel Analytics Performance Tuning Guide Published: 18 April 2003 |