測試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資訊。