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

 

 

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 -- 与索引相关的数据字典视图