这个视图主要是通过执行Analysis Index index_name VALIDATE STRUCTURE语句产生的统计信息,注意这个视图是session级别的。
上述这个语句只是分析相关索引信息,并不会统计收集、更新索引的信息状态等,如若要统计收集、更新索引的状态,必须使用:
Alter Index index_name Compute Statistics;
INDEX_STATS
stores information from the last ANALYZE INDEX ... VALIDATE STRUCTURE
statement.
Note:
TheANALYZE 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 |
通过该视图我们可以判断一个所以是否需要重建:
对一个索引进行结构分析后,如果该索引占用超过了一个数据块,且满足以下条件之一;
B-tree树的高度大于3;
使用百分比低于75%;
数据删除率大于15%.
SQL> select btree_space, -- if > 8192(块的大小)
height, -- if > 3
pct_used, -- if < 75
del_lf_rows/(decode(lf_rows,0,1,lf_rows)) *100 as deleted_pct -- if > 15%
from index_stats;
-The End-