index_stats视图用于查看索引结构相关信息

index_stats视图存储最后一次执行ANALYZE INDEX … VALIDATE STRUCTURE语句的统计信息。

这个视图主要是通过执行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:

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

ColumnDatatypeNULLDescriptionHEIGHTNUMBER Height of the B-TreeBLOCKSNUMBERNOT NULLBlocks allocated to the segmentNAMEVARCHAR2(30)NOT NULLName of the indexPARTITION_NAMEVARCHAR2(30) Name of the partition of the index which was analyzed. If the index is not partitioned, NULL is returned.LF_ROWSNUMBER Number of leaf rows (values in the index)LF_BLKSNUMBER Number of leaf blocks in the B-TreeLF_ROWS_LENNUMBER Sum of the lengths of all the leaf rowsLF_BLK_LENNUMBER Usable space in a leaf blockBR_ROWSNUMBER Number of branch rows in the B-TreeBR_BLKSNUMBER Number of branch blocks in the B-TreeBR_ROWS_LENNUMBER Sum of the lengths of all the branch blocks in the B-TreeBR_BLK_LENNUMBER Usable space in a branch blockDEL_LF_ROWSNUMBER Number of deleted leaf rows in the indexDEL_LF_ROWS_LENNUMBER Total length of all deleted rows in the indexDISTINCT_KEYSNUMBER Number of distinct keys in the index (may include rows that have been deleted)MOST_REPEATED_KEYNUMBER How many times the most repeated key is repeated (may include rows that have been deleted)BTREE_SPACENUMBER Total space currently allocated in the B-TreeUSED_SPACENUMBER Total space that is currently being used in the B-TreePCT_USEDNUMBER Percent of space allocated in the B-Tree that is being usedROWS_PER_KEYNUMBER Average number of rows per distinct key (this figure is calculated without consideration of deleted rows)BLKS_GETS_PER_ACCESSNUMBER 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_ROWSNUMBER Number of prefix rows (values in the index)PRE_ROWS_LENNUMBER Sum of lengths of all prefix rowsOPT_CMPR_COUNTNUMBER Optimal key compression lengthOPT_CMPR_PCTSAVENUMBER Corresponding space savings after anANALYZE

通过该视图我们可以判断一个所以是否需要重建:
对一个索引进行结构分析后，如果该索引占用超过了一个数据块，且满足以下条件之一;
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-

======================================================================

经常碰到人问我index 什么时候需要rebuild,今天看了个文章，记录了一下.

The INDEX_STATS view is useful for seeing how efficiently an index is using its space. Large indexes
have a tendency to become unbalanced over time if many deletions are in the table (and therefore
index) data. Your goal is to keep an eye on those large indexes with a view to keeping them balanced.
Note that the INDEX_STATS view is populated only if the table has been analyzed by using the
ANALYZE command, as follows:
SQL> ANALYZE index hr.emp_name_ix VALIDATE STRUCTURE;
Index analyzed.
The query in Listing 7-26 using the INDEX_STATS view helps determine whether you need to
rebuild the index. In the query, you should focus on the following columns in the INDEX_STATS view

to determine if your index is a candidate for a rebuild:
• HEIGHT: This column refers to the height of the B-tree index, and it’s usually at the 1, 2, or 3
level. If large inserts push the index height beyond a level of 4, it’s time to rebuild, which flattens
the B-tree.
• DEL_LF_ROWS: This is the number of leaf nodes deleted due to the deletion of rows. Oracle
doesn’t rebuild indexes automatically and, consequently, too many deleted leaf rows can lead
to an unbalanced B-tree.
• BLK_GETS_PER_ACCESS: You can look at the BLK_GETS_PER_ACCESS column to see how
much logical I/O it takes to retrieve data from the index. If this row shows a double-digit
number, you should probably start rebuilding the index.

Using the INDEX_STATS View to Determine Whether to Rebuild an Index
SQL> SELECT height, /*Height of the B-Tree*/
2 blocks, /* Blocks in the index segment */
3 name, /*index name */
4 lf_rows, /* number of leaf rows in the index */
5 lf_blks, /* number of leaf blocks in the index */
6 del_lf_rows, /* number of deleted leaf rows
in the index */
7 rows_per_key /* average number of rows
per distinct key */
8 blk_gets_per_access /* consistent mode block reads (gets) */
8 FROM INDEX_STATS
9* WHERE name='EMP_NAME_IX';
HEIGHT BLOCK LF_ROWS LF_BLKS DEL_LF_ROWS ROWS_PER_KEY BLK_GETS
------ ----------- ------- -------- ----------- ------------ ---------
16 EMP_NAME_IX 107 1 0 1

注释：参看《深入研究B树索引》有关该视图的使用