ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > Linux操作系统 > Linux操作系统 > [实验]SHRINK和MOVE的对比

[实验]SHRINK和MOVE的对比

原创 Linux操作系统 作者：尛样儿 时间：2010-08-25 16:15:40 0 删除 编辑


sqlplus test/test

SQL*Plus: Release 10.2.0.4.0 - Production on Wed Aug 25 14:52:01 2010

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create table t1 (num number);

Table created.

SQL> create index idx_t1 on t1(num);

Index created.

SQL> create table t2 (num number);

Table created.

SQL> create index idx_t2 on t2(num);

Index created.

SQL> create or replace trigger t_tri
after insert on t1
for each row
declare
begin
insert into t2 values(:new.num);
end t_tri;  2    3    4    5    6    7 
  8  /

Trigger created.


测试数据准备：

SQL> begin
  2  for i in 1..10 loop
  3  for j in 1..10000 loop
  4  insert into t1 values (j);
  5  ednd loop;
  6  end loop;
  7  end;
  8  /

PL/SQL procedure successfully completed.

SQL> select count(1) from t1;

  COUNT(1)
----------
    100000

SQL> select count(1) from t2;

  COUNT(1)
----------
    100000

SQL> commit;

Commit complete.


收集表的统计信息：


SQL> begin
  2  dbms_stats.gather_table_stats(ownname=>'TEST',tabname=>'T1');
  3  dbms_stats.gather_table_stats(ownname=>'TEST',tabname=>'T2');
  4  end;
  5  /

PL/SQL procedure successfully completed.

SQL> select table_name,num_rows,blocks,empty_blocks from user_tables where table_name in('T1','T2');

TABLE_NAME                       NUM_ROWS     BLOCKS EMPTY_BLOCKS
------------------------------ ---------- ---------- ------------
T1                                 100000        244            0
T2                                 100000        244            0

SQL> col segment_name format a10
SQL>  select segment_name,bytes from user_segments where segment_name in ('T1','T2');

SEGMENT_NA      BYTES
---------- ----------
T1            2097152
T2            2097152


两个表的统计信息，所分配空间都完全相同。


SQL> select min(dbms_rowid.rowid_block_number(rowid)) min ,
max(dbms_rowid.rowid_block_number(rowid)) max,
max(dbms_rowid.rowid_block_number(rowid)) -min(dbms_rowid.rowid_block_number(rowid)) "MAX-MIN"
from t1;  2    3    4 

       MIN        MAX    MAX-MIN
---------- ---------- ----------
     14620      15624       1004

SQL> select min(dbms_rowid.rowid_block_number(rowid)) min ,
max(dbms_rowid.rowid_block_number(rowid)) max,
max(dbms_rowid.rowid_block_number(rowid)) -min(dbms_rowid.rowid_block_number(rowid)) "MAX-MIN"
from t2;  2    3    4 

       MIN        MAX    MAX-MIN
---------- ---------- ----------
     14604      15752       1148


显示两张表的最小块号和最大块号。


SQL> analyze index idx_t1 validate structure;

Index analyzed.

SQL> col name format a10
SQL> select name,lf_rows_len,lf_blk_len,
br_rows_len,br_blk_len,
del_lf_rows,del_lf_rows_len,
btree_space,used_space
from index_stats;  2    3    4    5 

NAME       LF_ROWS_LEN LF_BLK_LEN BR_ROWS_LEN BR_BLK_LEN DEL_LF_ROWS DEL_LF_ROWS_LEN BTREE_SPACE USED_SPACE
---------- ----------- ---------- ----------- ---------- ----------- --------------- ----------- ----------
IDX_T1         1498010       7996        3285       8028           0               0     2406828    1501295


T1表的统计信息。由于没有任何删除数据,DEL_LF_ROWS和DEL_LF_ROWS_LEN都是0。


SQL> analyze index idx_t2 validate structure;

Index analyzed.

SQL> select name,lf_rows_len,lf_blk_len,
br_rows_len,br_blk_len,
del_lf_rows,del_lf_rows_len,
btree_space,used_space
from index_stats;  2    3    4    5 

NAME       LF_ROWS_LEN LF_BLK_LEN BR_ROWS_LEN BR_BLK_LEN DEL_LF_ROWS DEL_LF_ROWS_LEN BTREE_SPACE USED_SPACE
---------- ----------- ---------- ----------- ---------- ----------- --------------- ----------- ----------
IDX_T2         1498010       7996        3285       8028           0               0     2406828    1501295


T2表和T1表情况相同。


SQL> select segment_name,bytes from user_segments where segment_name in('IDX_T1','IDX_T2');

SEGMENT_NA      BYTES
---------- ----------
IDX_T1        3145728
IDX_T2        3145728


两个索引所分配的空间也是相同大小的。


删除部分数据：


SQL> delete from t1 where num >=2000 and num <=7000;

50010 rows deleted.

SQL> delete from t2 where num >=2000 and num <=7000;

50010 rows deleted.

SQL> commit;

Commit complete.


收集表的统计信息：


SQL> begin
  2  dbms_stats.gather_table_stats(ownname=>'TEST',tabname=>'T1');
  3  dbms_stats.gather_table_stats(ownname=>'TEST',tabname=>'T2');
  4  end;
  5  /

PL/SQL procedure successfully completed.

SQL> select table_name,num_rows,blocks,empty_blocks from user_tables where table_name in('T1','T2');

SQL> select table_name,num_rows,blocks,empty_blocks from user_tables where table_name in('T1','T2');

TABLE_NAME                       NUM_ROWS     BLOCKS EMPTY_BLOCKS
------------------------------ ---------- ---------- ------------
T1                                  49536        244            0
T2                                  50444        244            0

SQL> select count(1) from t1;

  COUNT(1)
----------
     49990

SQL> select count(1) from t2;

  COUNT(1)
----------
     49990


从NUM_ROWS可以看出统计信息并不是非常的准确，但是两个表所占用的空间是没有发生变化的，也就是说执行DELETE语句高水位线是不会发生变化的。这就是为什么表只有很少的数据或者根本没有数据但执行全表扫描的时候依然会花费很长的时间，全表扫描是从高水位线以下扫描的。


SQL> select segment_name,bytes from user_segments where segment_name in ('T1','T2');

SEGMENT_NA      BYTES
---------- ----------
T1            2097152
T2            2097152

分配给两个表的空间也没有发生任何变化。


SQL> select min(dbms_rowid.rowid_block_number(rowid)) min ,
max(dbms_rowid.rowid_block_number(rowid)) max,
max(dbms_rowid.rowid_block_number(rowid)) -min(dbms_rowid.rowid_block_number(rowid)) "MAX-MIN"
from t1;  2    3    4 

       MIN        MAX    MAX-MIN
---------- ---------- ----------
     14620      15607        987
 
SQL> select min(dbms_rowid.rowid_block_number(rowid)) min ,
max(dbms_rowid.rowid_block_number(rowid)) max,
max(dbms_rowid.rowid_block_number(rowid)) -min(dbms_rowid.rowid_block_number(rowid)) "MAX-MIN"
from t2;  2    3    4 

       MIN        MAX    MAX-MIN
---------- ---------- ----------
     14604      15735       1131


最小的块号没有发生变化，但最大的块号发生了变化，删除的是中间的数据，但最大的块号发生了变化，说明Oracle分配extent是没有规律的。


SQL> select segment_name,bytes from user_segments where segment_name in ('IDX_T1','IDX_T2');

SEGMENT_NA      BYTES
---------- ----------
IDX_T1        3145728
IDX_T2        3145728

SQL> analyze index idx_t1 validate structure;

Index analyzed.

SQL> select name,lf_rows_len,lf_blk_len,
br_rows_len,br_blk_len,
del_lf_rows,del_lf_rows_len,
btree_space,used_space
from index_stats;  2    3    4    5 

NAME       LF_ROWS_LEN LF_BLK_LEN BR_ROWS_LEN BR_BLK_LEN DEL_LF_ROWS DEL_LF_ROWS_LEN BTREE_SPACE USED_SPACE
---------- ----------- ---------- ----------- ---------- ----------- --------------- ----------- ----------
IDX_T1         1498010       7996        3285       8028       50010          749640     2406828    1501295


分配给索引的空间和索引所占用的空间并没有发生变化。DEL_LF_ROWS已经有值了，索引中已经开始存在“碎片”，这段空间是不能被使用的，对查询有一定的性能影响。


SQL> analyze index idx_t2 validate structure;

Index analyzed.

SQL> select name,lf_rows_len,lf_blk_len,
br_rows_len,br_blk_len,
del_lf_rows,del_lf_rows_len,
btree_space,used_space
from index_stats;  2    3    4    5 

NAME       LF_ROWS_LEN LF_BLK_LEN BR_ROWS_LEN BR_BLK_LEN DEL_LF_ROWS DEL_LF_ROWS_LEN BTREE_SPACE USED_SPACE
---------- ----------- ---------- ----------- ---------- ----------- --------------- ----------- ----------
IDX_T2         1498010       7996        3285       8028       50010          749640     2406828    1501295


T2表的索引和T1表的索引情况相同。


SQL> select index_name,status from user_indexes where
index_name in ('IDX_T1','IDX_T2');

INDEX_NAME                     STATUS
------------------------------ --------
IDX_T1                         VALID
IDX_T2                         VALID

SQL> alter table t1 enable row movement;

Table altered.

SQL> alter table t1 shrink space;

Table altered.

SQL> alter table t2 move;

Table altered.

SQL> select index_name,status from user_indexes where index_name in ('IDX_T1','IDX_T2');

INDEX_NAME                     STATUS
------------------------------ --------
IDX_T1                         VALID
IDX_T2                         UNUSABLE


我们分别对比shrink和move的区别和优劣。当对T2表执行move之后，表上的索引变得无效。


SQL> begin
  2  dbms_stats.gather_table_stats(ownname=>'TEST',tabname=>'T1');
  3  dbms_stats.gather_table_stats(ownname=>'TEST',tabname=>'T2');
  4  end;
  5  /
begin
*
ERROR at line 1:
ORA-20000: index "TEST"."IDX_T2"  or partition of such index is in unusable state
ORA-06512: at "SYS.DBMS_STATS", line 13437
ORA-06512: at "SYS.DBMS_STATS", line 13457
ORA-06512: at line 3


由于T2表上的索引无效，在收集T2表的统计量的时候也报错了。


SQL> analyze index idx_t1 validate structure;

Index analyzed.

SQL> select name,lf_rows_len,lf_blk_len,
br_rows_len,br_blk_len,
del_lf_rows,del_lf_rows_len,
btree_space,used_space
from index_stats;  2    3    4    5 

NAME       LF_ROWS_LEN LF_BLK_LEN BR_ROWS_LEN BR_BLK_LEN DEL_LF_ROWS DEL_LF_ROWS_LEN BTREE_SPACE USED_SPACE
---------- ----------- ---------- ----------- ---------- ----------- --------------- ----------- ----------
IDX_T1         1563466       7996        3285       8028       54382          815096     2406828    1566751


对T1表做shrink之后，索引所占用空间变大了。


SQL> analyze index idx_t2 validate structure;
analyze index idx_t2 validate structure
*
ERROR at line 1:
ORA-01502: index 'TEST.IDX_T2' or partition of such index is in unusable state


T2表的索引无效，分析失败。


SQL> select segment_name,bytes from user_segments where segment_name='T1';

SEGMENT_NA      BYTES
---------- ----------
T1             720896


分析T1表：


SQL> begin
  2  dbms_stats.gather_table_stats(ownname=>'TEST',tabname=>'T1');
  3  end;
  4  /

PL/SQL procedure successfully completed.

SQL> select table_name,num_rows,blocks,empty_blocks from user_tables where table_name='T1';

TABLE_NAME                       NUM_ROWS     BLOCKS EMPTY_BLOCKS
------------------------------ ---------- ---------- ------------
T1                                  50126         76            0


对T1表shrink之后，分配的空间和所占空间都下降了很多，Oracle对blocks数据进行了重新的整理。


SQL> alter index idx_t2 rebuild;

Index altered.

SQL> analyze index idx_t2 validate structure;

Index analyzed.

SQL> select name,lf_rows_len,lf_blk_len,
br_rows_len,br_blk_len,
del_lf_rows,del_lf_rows_len,
btree_space,used_space
from index_stats;  2    3    4    5 

NAME       LF_ROWS_LEN LF_BLK_LEN BR_ROWS_LEN BR_BLK_LEN DEL_LF_ROWS DEL_LF_ROWS_LEN BTREE_SPACE USED_SPACE
---------- ----------- ---------- ----------- ---------- ----------- --------------- ----------- ----------
IDX_T2          748370       7996        1527       8028           0               0      847608     749897


由于T2表上的索引无效，只能重建，重建后索引所占空间变小，不存在所谓的“碎片”。


SQL> begin
  2  dbms_stats.gather_table_stats(ownname=>'TEST',tabname=>'T2');
  3  end;
  4  /

PL/SQL procedure successfully completed.

SQL> select table_name,num_rows,blocks,empty_blocks from user_tables where table_name='T2';

TABLE_NAME                       NUM_ROWS     BLOCKS EMPTY_BLOCKS
------------------------------ ---------- ---------- ------------
T2                                  49763         85            0

SQL> select segment_name,bytes from user_segments where segment_name='T2';

SEGMENT_NA      BYTES
---------- ----------
T2             720896


T2表执行的是MOVE，MOVE之后，效果跟shrink很类似，高水位线和分配的空间都下降了不少，Oracle对数据进行了重新的整理。


SQL> alter table t1 shrink space cascade;

Table altered.

SQL> analyze index idx_t1 validate structure;

Index analyzed.

SQL>  select name,lf_rows_len,lf_blk_len,
br_rows_len,br_blk_len,
del_lf_rows,del_lf_rows_len,
btree_space,used_space
from index_stats;  2    3    4    5 

NAME       LF_ROWS_LEN LF_BLK_LEN BR_ROWS_LEN BR_BLK_LEN DEL_LF_ROWS DEL_LF_ROWS_LEN BTREE_SPACE USED_SPACE
---------- ----------- ---------- ----------- ---------- ----------- --------------- ----------- ----------
IDX_T1          748370       7996        1502       8028           0               0      847608     749872


如果我们在shrink的时候加上cascade关键字，那么索引的数据也会被重新整理，跟rebuild后的效果相同。


SQL> select min(dbms_rowid.rowid_block_number(rowid)) min ,
max(dbms_rowid.rowid_block_number(rowid)) max,
max(dbms_rowid.rowid_block_number(rowid)) -min(dbms_rowid.rowid_block_number(rowid)) "MAX-MIN"
from t1;  2    3    4 

       MIN        MAX    MAX-MIN
---------- ---------- ----------
     14620      15372        752

SQL> select min(dbms_rowid.rowid_block_number(rowid)) min ,
max(dbms_rowid.rowid_block_number(rowid)) max,
max(dbms_rowid.rowid_block_number(rowid)) -min(dbms_rowid.rowid_block_number(rowid)) "MAX-MIN"
from t2;
  2    3    4 

       MIN        MAX    MAX-MIN
---------- ---------- ----------
     15388      15509        121


SHRINK的MIN并没有发生变化，而MOVE后的MIN发生了变化。shrink的MAX-MIN比MOVE的MAX-MIN要大很多。由此看出，shrink命令并不会使最小的块发生变化，而按照最小块的位置开始整理数据。而MOVE使最小块发生了变化，从而使extent挨得更近一些。


总结：
在日常的数据库运行过程中，时间久了会使表出现大量的空块产生，大量的空块不能及时被重新使用，无疑对全表扫描来说是不利的。同时，表所对应的索引也会出现大量的“空隙”，这些“空隙”是无法被重新使用的。这对索引的扫描，特别是INDEX FULL SCAN和INDEX RANGE SCAN是不利的。
通常我们处理这种问题有2种方法：
SHRINK和MOVE。这2种方法都可以达到相同的效果：使表中的数据进行重新整理，使所占用的空间和Oracle分配的空间都能重新分配到合适的值。当然也有缺点，MOVE后的表上的索引变得无效，需要重建。重建后的索引的“碎片”也就不存在了。SHRINK如果加上CASCADE关键字，那么索引数据也会得到重新整理，跟重建后的索引达到的效果相同。重最终的执行效果来看，move是把整个表进行迁移能够使exent靠得更紧，更适合表的扫描。
从以上描述可以知道，如果想同时整理表跟索引，shrink只需要一步搞定，而move需要2步(重建索引)，但是move整理的效果更好(extent挨得更近)。同时还有一个需要指出的是shrink之后一些高级的特性，例如flashback将不能使用(如果没记错的话，常在ITPUB上看到shrink也会带来一些其他的问题)。
综上所述，虽然Move需要更多的步骤，但是move的效果更好，而且move之后不会影响其他高级特性的使用。索引重建的时间并不一定比整理的时间慢，所以推荐大家使用MOVE更好一点。
当然，如果表的访问频率很高，这2种方法就都不适合了，通常使用重定义表来完成。另外exp、imp也可以达到表数据的整理。exp的时候加上compress=y也可以压缩表的空间。

来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/23135684/viewspace-671844/ ，如需转载，请注明出处，否则将追究法律责任。

0
0
上一篇： 关于约束那些事儿
下一篇： Oracle 10gR2 JDBC介绍
请登录后发表评论 登录
全部评论
尛样儿
Oracle数据库管理员，Oracle数据库系统构架员；2012年7月出版《构建最高可用Oracle数据库系统：Oracle 11gR2 RAC管理、维护与性能优化》一书；Oracle 10g OCM。

注册时间：2010-01-05

  • 博文量
    483
  • 访问量
    5650559

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us 编辑准则

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593