重建整合索引方法-对比测试
重建整合索引方法-对比测试
创建测试表
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;
索引已分析
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总数没有变但是叶子块由515变为343，减少了172。btree_space变小了，这个应该是索引的真实数据占用的空间，而block是为索引分配的空间。
SQL> alter index ind_t1 shrink space;
索引已更改。
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 360 343 2459238 8000 1 342 4067 8032 2752032
2463305 90
shrink space使blocks变小，真正释放了空间
新创建一个测试表
SQL> create table t2 as select rownum a,rpad('A',20,'B') b from dual connect by level<100000;
表已创建。
SQL> create index ind_t2 on t2(a,b);
索引已创建。
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 515 3688857 8000 1 514 6117 8032 4128032
3694974 90
SQL> delete from t2 where mod(a,3)=1;
已删除33333行。
SQL> commit;
提交完成。
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 515 3688857 8000 1 514 6117 8032 4128032
3694974 90
只是进行delete后做分析，统计出信息并没有变化
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 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 rownum 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，但是对性能影响会更大一些。
本站文章为和通数据库网友分享或者投稿，欢迎任何形式的转载，但请务必注明出处.
同时文章内容如有侵犯了您的权益，请联系QQ：970679559，我们会在尽快处理。