2012-11-5 11gR2 "ADMINISTRATOR'S GUIDE" page 647

本文探讨了监控索引空间使用的策略，并提供了分析、验证索引效率、检查空间使用百分比的方法。通过定期操作分析、验证索引并检查PCT_USED，可以发现索引空间使用率的变化，进而决定是否需要通过合并或重建索引来优化空间使用。

Consider Costs and Benefits of Coalescing or Rebuilding Indexes -- COALESCE与REBUILD

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:
-- Analyzing statistics
-- Validating the index
-- Checking PCT_USED
-- Dropping and rebuilding (or coalescing) the index
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

Indexes Data Dictionary Views -- 与索引相关的数据字典视图