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