Oracle 11g索引的碎片分析
发布时间：2020-07-21 08:38:39 所属栏目：Oracle 来源：互联网
导读：数据被删除之后，索引只是加了一个标记，并没有真正的删除，这样可以查看碎片率。 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
|
数据被删除之后，索引只是加了一个标记，并没有真正的删除，这样可以查看碎片率。 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（编辑：鄂州站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
