oracle--索引

最新推荐文章于 2025-08-04 23:14:52 发布

weixin_30319153

最新推荐文章于 2025-08-04 23:14:52 发布

阅读量135

点赞数

CC 4.0 BY-SA版权

文章标签：数据库

原文链接：http://www.cnblogs.com/eadela/p/11562136.html

oracle--索引

索引碎片整理实例

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;