测试4——shrink和move产生的redo log量的比较

shrink过程和move过程日志产生量比较：

关于日志的问题,我们对比了同样数据量和分布状况的两张table,在move和shrink下生成的redo size(table上没有index的情况下):

SQL> select tablespace_name,SEGMENT_SPACE_MANAGEMENT from dba_tablespaces  where tablespace_name ='ASSMTEST';
TABLESPACE_NAME SEGMENT_SPACE_MANAGEMENT
------------------------------ ------------------------
ASSMTEST AUTO

SQL> create table my_objects  as select * from dba_objects ;

SQL> create table my_objects1  as select * from dba_objects ;

SQL> select bytes/1024/1024 from user_segments where segment_name = 'MY_OBJECTS';

BYTES/1024/1024
---------------

9

SQL> delete from my_objects where object_name like '%C%';
7278 rows deleted
SQL> delete from my_objects1 where object_name like '%C%';
7278 rows deleted
SQL> delete from my_objects where object_name like '%U%';
2732 rows deleted
SQL> delete from my_objects1 where object_name like '%U%';
2732 rows deleted
SQL> commit;
Commit complete
SQL> alter table my_objects enable row movement;

Table altered

SQL>  select value from v$mystat, v$statname where v$mystat.statistic# = v$statname.statistic# and v$statname.name = 'redo size';
     VALUE
----------
  84466796


SQL> alter table my_objects shrink space;
Table altered.


SQL>  select value from v$mystat, v$statname where v$mystat.statistic# = v$statname.statistic# and v$statname.name = 'redo size';
     VALUE
----------
  97945584

SQL> alter table my_objects1 move;

Table altered

SQL>  select value from v$mystat, v$statname where v$mystat.statistic# = v$statname.statistic# and v$statname.name = 'redo size';
     VALUE
----------
  98004004

对于table my_objects,进行shrink,产生了 97945584 - 84466796 = 13 478 788,约13.5M的redo

;对table my_objects1进行move,产生了98004004-97945584= 58 420,约 58K 的 redo size.

那么,与move比较起来,shrink的日志写要大得多.

其最根本的原因，我们可以从move和shrink的原理中找到，shrink是行的移动，相当于对数据块内的数据行删除然后插入的操作，会产生大量的undo redo信息；

而move是对数据块的移动操作，不会产生dml操作类似的undo信息。