Oracle 11g索引的碎片分析

2/13/2017来源:SQL技巧人气:2220

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

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