测试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%';

SQL> delete from my_objects where object_name like '%U%';

2732 rows deleted

SQL> delete from my_objects1 where object_name like '%U%';

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.

  97945584

SQL> alter table my_objects1 move;

  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信息。