索引碎片整理实例
select * from user_indexes;
select * from user_ind_columns;
--建立表、索引:
create table t (id int);
create index ind_1 on t(id);
执行插入记录:
begin
for i in 1..1000000 loop
insert into t values (i);
if mod(i, 100)=0 then commit;
end if;
end loop;
end;
--分析索引:
analyze index ind_1 validate structure;
select name,HEIGHT,PCT_USED,DEL_LF_ROWS/LF_ROWS from index_stats;
delete t where rownum<700000;
alter index ind_1 rebuild [online] [tablespace name];
--实例:
select count(*) from t;
select name,HEIGHT,PCT_USED,DEL_LF_ROWS/LF_ROWS from index_stats;
analyze index ind_1 validate structure;
select name,HEIGHT,PCT_USED,DEL_LF_ROWS/LF_ROWS from index_stats;
delete t where rownum < 700000;
commit;
select name,HEIGHT,PCT_USED,DEL_LF_ROWS/LF_ROWS from index_stats;
analyze index ind_1 validate structure;
select name,HEIGHT,PCT_USED,DEL_LF_ROWS/LF_ROWS from index_stats;
alter index ind_1 rebuild online ;--[tablespace name]
select name,HEIGHT,PCT_USED,DEL_LF_ROWS/LF_ROWS from index_stats;
analyze index ind_1 validate structure;
select name,HEIGHT,PCT_USED,DEL_LF_ROWS/LF_ROWS from index_stats;