重建整合索引方法-对比测试
重建整合索引方法-对比测试
创建测试表
SQL> create table t1 as select rownum a,rpad('A',20,'B') b from dual connect by level<100000;
表已创建。
SQL> select count(*) from t1;
COUNT(*)
----------
99999
SQL> create index ind_t1 on t1(a,b);
索引已创建。
SQL> analyze index ind_t1 validate structure;
索引已分析
SQL> select height,blocks,lf_blks,lf_rows_len,lf_blk_len,br_blks,br_rows,
2 br_rows_len,br_blk_len,btree_space,used_space,pct_used from index_stats;
HEIGHT BLOCKS LF_BLKS LF_ROWS_LEN LF_BLK_LEN BR_BLKS BR_ROWS BR_ROWS_LEN BR_BLK_LEN BTREE_SPACE
---------- ---------- ---------- ----------- ---------- ---------- ---------- ----------- ---------- -----------
USED_SPACE PCT_USED
---------- ----------
2 640 515 3688857 8000 1 514 6117 8032 4128032
3694974 90
删除1/3行
SQL> delete t1 where mod(a,3)=1;
已删除33333行。
SQL> commit;
提交完成。
查看redo情况
SQL> select vs.name,ms.value from v$mystat ms,v$sysstat vs where ms.statistic#=vs.statistic# and vs.name in ('redo size','consistent gets');
NAME VALUE
---------------------------------------------------------------- ----------
consistent gets 3485
redo size 20580416
SQL> alter index ind_t1 coalesce;
索引已更改。
SQL> select vs.name,ms.value from v$mystat ms,v$sysstat vs where ms.statistic#=vs.statistic# and vs.name in ('redo size','consistent gets');
NAME VALUE
---------------------------------------------------------------- ----------
consistent gets 3501
redo size 27632084
coalesce=27632084-20580416 产生约6.7m redo
SQL> analyze index ind_t1 validate structure;
索引已分析
