重建整合索引方法-对比测试(三)
9715
redo size 50275964
SQL> alter index ind_t2 shrink space compact;
索引已更改。
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 10022
redo size 59333252
shrink space compact产生redo为59333252-50275964，约为8.6m
SQL> analyze index ind_t2 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 343 2459238 8000 1 342 4067 8032 2752032
2463305 90
此时blocks大小并没有变化，只是叶子块减少与coalesce一样多
SQL> alter index ind_t2 shrink space;
索引已更改。
SQL> analyze index ind_t2 validate structure;
索引已分析
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 11580
redo size 59393732
继续做shrink space几乎没有产生redo
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 360 343 2459238 8000 1 342 4067 8032 2752032
2463305 90
blocks 大小变小了哦
总结：coalesce与shrink space compact效果大致相同，只是多产生了一点redo信息，没有真正释放出空间。而shrink space会真正释放空间。
接下来看一下重建索引
SQL> create table t3 as select rownu