Oracle 11g索引的碎片分析

  数据被删除之后，索引只是加了一个标记，并没有真正的删除，在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,
       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 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,
       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 89.53%               84