Oracle 11g索引的碎片分析

2019-11-09 13:32:30

字体：大中小

来源：转载

供稿：网友

数据被删除之后，索引只是加了一个标记，并没有真正的删除，这样可以查看碎片率。

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

上一篇：共享IP下，Navicat与Mysql数据库的连接

下一篇：mac上的mysql管理工具sequel pro