重建整合索引方法-对比测试(四)
m id,rpad('A',20,'B') name from dual connect
by level<100000;
表已创建。
SQL> create index ind_t3 on t3(id ,name);
索引已创建。
SQL> analyze index ind_t3 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
SQL> delete t3 where mod(id,3)=1;
已删除33333行。
SQL> analyze index ind_t3 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
SQL> select vs.name,ms.value from v$mystat ms,v$sysstat vs where ms.statistic#=v
s.statistic# and vs.name in ('redo size','consistent gets');
NAME VALUE
---------------------------------------------------------------- ----------
consistent gets 15210
redo size 79973788
SQL> alter index ind_t3 rebuild;
索引已更改。
SQL> analyze index ind_t3 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 384 343 2459238 8000 1 342
4067 8032 2752032
2463305 90
SQL> select vs.name,ms.value from v$mystat ms,v$sysstat vs where ms.statistic#=v
s.statistic# and vs.name in ('redo size','consistent gets');
NAME VALUE
---------------------------------------------------------------- ----------
consistent gets 16656
redo size 82859236
SQL>
redo产生很少但是用了更多地consistent gets,因为他用的是先drop再create
这种rebuild会堵塞dml操作，
如果使用rebuild online则不会堵塞dml，但是对性能影响会更大一些。