Skip Headers
Oracle® Database Administrator's Guide
11g Release 1 (11.1)

B28310-04
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

Monitoring Space Use of Indexes

If key values in an index are inserted, updated, and deleted frequently, the index can lose its acquired space efficiently over time. Monitor index efficiency of space usage at regular intervals by first analyzing the index structure, using the ANALYZE INDEX...VALIDATE STRUCTURE statement, and then querying the INDEX_STATS view:

SELECT PCT_USED FROM INDEX_STATS WHERE NAME = 'index';

The percentage of index space usage varies according to how often index keys are inserted, updated, or deleted. Develop a history of average efficiency of space usage for an index by performing the following sequence of operations several times:

When you find that index space usage drops below its average, you can condense the index space by dropping the index and rebuilding it, or coalescing it.