我们在使用“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
6.小结
通过这种方法对索引进行分析后,可以快速的得到索引的基本情况,为进一步对其进行优化起到了非常大的帮助。
index_stats视图的其他列也同样很有意义,值得细细品味。
secooler
09.11.27
-- The End --
通过这个实验简单展示一下几个重要的信息列。
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 statisticsColumn | 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博客 ” ,链接:http://blog.itpub.net/519536/viewspace-620882/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/519536/viewspace-620882/