oracle 索引碎片影响,Oracle 11g索引的碎片分析

最新推荐文章于 2024-05-29 10:18:43 发布

影落飞神

最新推荐文章于 2024-05-29 10:18:43 发布

阅读量146

点赞数

文章标签： oracle 索引碎片影响

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