Oracle7 Server Administrator's Guide

Monitoring Space Use of Indexes

If key values in an index are inserted, updated, and deleted frequently, the index may or may not use its acquired space efficiently over time. Monitor an index's efficiency of space usage at regular intervals by first analyzing the index's structure and then querying the INDEX_STATS view:

SELECT pct_used FROM sys.index_stats WHERE name = 'indexname';

The percentage of an index's space usage will vary according to how often index keys are inserted, updated, or deleted. Develop a history of an index's average efficiency of space usage by performing the following sequence of operations several times: validating the index, checking PCT_USED, and dropping and re-creating the index. When you find that an index's space usage drops below its average, you can condense the index's space by dropping the index and re-creating or re-building it.

See Also: For information about analyzing an index's structure, see "Analyzing Tables, Indexes, and Clusters" .