Experiment: Comparison of redo logs generated by table shrink and move in Oracle

1 move Experiment

SQL> Create Table my_objects_move tablespace assm as select * From all_objects where rownum <20000;

Table created

 

SQL> select Value

2 from V $ mystat, V $ statname

3 where V $ mystat. Statistic # = V $ statname. Statistic #

4 and V $ statname. Name = 'redo size ';

Value

----------

2317832

 

SQL> Delete from my_objects_move where object_name like '% C % ';

7546 rows deleted

SQL> Delete from my_objects_move where object_name like '% u % ';

2959 rows deleted

SQL> commit;

Commit complete

 

SQL> select Value

2 from V $ mystat, V $ statname

3 where V $ mystat. Statistic # = V $ statname. Statistic #

4 and V $ statname. Name = 'redo size ';

Value

----------

6159912

 

SQL> ALTER TABLE my_objects_move move;

Table altered

 

SQL> select Value

2 from V $ mystat, V $ statname

3 where V $ mystat. Statistic # = V $ statname. Statistic #

4 and V $ statname. Name = 'redo size ';

Value

----------

7265820

 

SQL> select (7265820-6159912)/1024/1024 "redo_size (m)" from dual;

Redo_size (m)

------------

1.0546760559

2 shrink lab

SQL> Create Table my_objects tablespace assm as select * From all_objects where rownum <20000;

Table created

 

SQL> select Value

2 from V $ mystat, V $ statname

3 where V $ mystat. Statistic # = V $ statname. Statistic #

4 and V $ statname. Name = 'redo size ';

Value

----------

2315104

 

SQL> Delete from my_objects where object_name like '% C % ';

7546 rows deleted

SQL> Delete from my_objects where object_name like '% u % ';

2959 rows deleted

SQL> commit;

Commit complete

 

SQL> select Value

2 from V $ mystat, V $ statname

3 where V $ mystat. Statistic # = V $ statname. Statistic #

4 and V $ statname. Name = 'redo size ';

Value

----------

6157428

 

SQL> ALTER TABLE my_objects enable row movement;

Table altered

SQL> ALTER TABLE my_objects shrink space;

Table altered

 

SQL> select Value

2 from V $ mystat, V $ statname

3 where V $ mystat. Statistic # = V $ statname. Statistic #

4 and V $ statname. Name = 'redo size ';

Value

----------

11034900

 

SQL> select (11034900-6157428)/1024/1024 "redo_size (m)" from dual;

Redo_size (m)

------------

4.6515197753

3 conclusion

Fewer logs are generated during move than shrink.