oracle–索引

• 2019 年 10 月 10 日
• 筆記

索引碎片整理实例

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;