我们在使用“validate structure”对索引进行分析后，通过查询index_stats可以得到很多有关索引本身的属性信息。
通过这个实验简单展示一下几个重要的信息列。

1.创建测试表t，在t表的x列上存在主键。

2.对索引（主键）进行分析
sec@ora10g> analyze index pk_t validate structure;

Index analyzed.

3.查询分析之后的结果
sec@ora10g> select lf_blks, br_blks, used_space, opt_cmpr_count, opt_cmpr_pctsave from index_stats;

LF_BLKS BR_BLKS USED_SPACE OPT_CMPR_COUNT OPT_CMPR_PCTSAVE
---------- ---------- ---------- -------------- ----------------
2088 4 15002737 0 0

4.所用该字段注释如下
LF_BLKS：索引使用的叶子块（数据所在的块）数目；
BR_BLKS：分支块（索引结构中导航所用的块）数目；
USED_SPACE：索引使用的空间，单位是字节；
OPT_CMPR_COUNT：最优压缩数；
OPT_CMPR_PCTSAVE：使用上面的最优压缩数后最优节省压缩百分比；

通过后面两个参数的指示，对于使用索引压缩技术来讲有直接的指导和借鉴意义。

5.有关index_stats视图的官方文档的解释附在此处，方便大家参考。
http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/statviews_4216.htm

INDEX_STATS

INDEX_STATS stores information from the last ANALYZE INDEX ... VALIDATE STRUCTURE statement.

Note:

The ANALYZE INDEX ... VALIDATE STRUCTURE OFFLINE statement must be used in order to collect statistics

Column	Datatype	NULL	Description
HEIGHT	NUMBER		Height of the B-Tree
BLOCKS	NUMBER	NOT NULL	Blocks allocated to the segment
NAME	VARCHAR2(30)	NOT NULL	Name of the index
PARTITION_NAME	VARCHAR2(30)		Name of the partition of the index which was analyzed. If the index is not partitioned, null is returned.
LF_ROWS	NUMBER		Number of leaf rows (values in the index)
LF_BLKS	NUMBER		Number of leaf blocks in the B-Tree
LF_ROWS_LEN	NUMBER		Sum of the lengths of all the leaf rows
LF_BLK_LEN	NUMBER		Usable space in a leaf block
BR_ROWS	NUMBER		Number of branch rows in the B-Tree
BR_BLKS	NUMBER		Number of branch blocks in the B-Tree
BR_ROWS_LEN	NUMBER		Sum of the lengths of all the branch blocks in the B-Tree
BR_BLK_LEN	NUMBER		Usable space in a branch block
DEL_LF_ROWS	NUMBER		Number of deleted leaf rows in the index
DEL_LF_ROWS_LEN	NUMBER		Total length of all deleted rows in the index
DISTINCT_KEYS	NUMBER		Number of distinct keys in the index (may include rows that have been deleted)
MOST_REPEATED_KEY	NUMBER		How many times the most repeated key is repeated (may include rows that have been deleted)
BTREE_SPACE	NUMBER		Total space currently allocated in the B-Tree
USED_SPACE	NUMBER		Total space that is currently being used in the B-Tree
PCT_USED	NUMBER		Percent of space allocated in the B-Tree that is being used
ROWS_PER_KEY	NUMBER		Average number of rows per distinct key (this figure is calculated without consideration of deleted rows)
BLKS_GETS_PER_ACCESS	NUMBER		Expected number of consistent mode block reads per row, assuming that a randomly chosen row is accessed using the index. Used to calculate the number of consistent reads that will occur during an index scan.
PRE_ROWS	NUMBER		Number of prefix rows (values in the index)
PRE_ROWS_LEN	NUMBER		Sum of lengths of all prefix rows
OPT_CMPR_COUNT	NUMBER		Optimal key compression length
OPT_CMPR_PCTSAVE	NUMBER		Corresponding space savings after an ANALYZE

6.小结
通过这种方法对索引进行分析后，可以快速的得到索引的基本情况，为进一步对其进行优化起到了非常大的帮助。
index_stats视图的其他列也同样很有意义，值得细细品味。

secooler
09.11.27

-- The End --

ITPub博客

【索引】使用索引分析快速得到索引的基本信息

INDEX_STATS

最新文章