Monitoring Space Use of Indexes【每日一译】--20121217

--监控索引空间的使用

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 it.

如果主键值在一个索引中被插入，更新和频繁删除，索引可能会失去它已经要求的空间的有效性

随着时间推移。监控索引空间使用的有效性在正常周期通过首先分析索引的架构，使用

ANALYZE INDEX ... VALIDATE STRUCTURE语句，然后查询INDEX_STATS视图：

***

索引空间使用百分比的变化依据索引键盘的插入，更新或者删除的频率。开发一个空间使用有平均有效性的

历史记录对于一个索引，通过顺序执行以下操作多次：

#

#

#

#

当你发现索引空间的使用下降到低于平均值时，你可以压缩索引空间通过DROP索引和REBUILDING它或者合并它。