对index进行分析，index_stats 表很有用。下面例子就结合index相关操作及 index_stats 的使用，对index进行分析。
SQL> select count(*) from index_stats;
COUNT(*)
----------
0
SQL> desc t
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NUMBER(38)
SCN NUMBER
SQL> create index t_idx on t(id);
Index created.
SQL> select * from t;
ID SCN
---------- ----------
1 576757
2 577958
3 578038
4 603361
5 603575
5 3
5 3
7 rows selected.
SQL> analyze index t_idx validate structure;
Index analyzed.
SQL> select count(*) from index_stats;
COUNT(*)
----------
1
SQL> select height,name,lf_rows,del_lf_rows from index_stats;
HEIGHT NAME LF_ROWS DEL_LF_ROWS
---------- ------------------------------ ---------- -----------
1 T_IDX 7 0
SQL> delete from t where id=5;
3 rows deleted.
SQL> commit;
SQL> analyze index t_idx validate structure;
Index analyzed.
SQL> select height,name,lf_rows,del_lf_rows from index_stats;
HEIGHT NAME LF_ROWS DEL_LF_ROWS
---------- ------------------------------ ---------- -----------
1 T_IDX 7 3
SQL> alter index t_idx rebuild;
Index altered.
SQL> select height,name,lf_rows,del_lf_rows from index_stats;
no rows selected
SQL> analyze index t_idx validate structure;
Index analyzed.
SQL> select height,name,lf_rows,del_lf_rows from index_stats;
HEIGHT NAME LF_ROWS DEL_LF_ROWS
---------- ------------------------------ ---------- -----------
1 T_IDX 4 0
SQL>
说明：
1.如果一个index的LF_ROWS与DEL_LF_ROWS比例超过15%，就需要rebulid了。
2.一般情况下，rebuild对自增长字段被删除列比较有用。
3.如果一个表批量导入大数据，先删除index，导入数据后，再create index；这样速度比较高。