Oracle 11g索引的碎片分析-蒲公英云

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