数据被删除之后，索引只是加了一个标记，并没有真正的删除，在11g之后，删除的索引在数据块中，下次插入数据或者删除数据时候会以数据块为单位清除。这样可以查看碎片率。
drop table test purge;
create table test as select * from dba_objects; insert into test select * from test; insert into test select * from test; commit; create index ind_t_object_id on test(object_id); analyze index ind_t_object_id validate structure; select s.height,round((del_lf_rows_len / lf_rows_len) * 100,2) || '%' frag_ratio,s.pct_used from index_stats s where s.name = 'IND_T_OBJECT_ID'; HEIGHT FRAG_RATIO PCT_USED ---------- ------------- ---------- 3 0% 90 delete from test where object_type in('SYNONYM','JAVA CLASS'); commit; analyze index ind_t_object_id validate structure; select s.height,s.pct_used from index_stats s where s.name = 'IND_T_OBJECT_ID'; HEIGHT FRAG_RATIO PCT_USED ---------- ------------- ---------- 3 70.23% 90 delete from test where object_type in('VIEW','INDEX','TABLE','TYPE'); commit; analyze index ind_t_object_id validate structure; select s.height,s.pct_used from index_stats s where s.name = 'IND_T_OBJECT_ID'; HEIGHT FRAG_RATIO PCT_USED ---------- --------------- ---------- 3 89.53% 84