论坛上有兄弟问http://www.itpub.net/thread-1338094-1-1.html

对于redo而言，不仅仅需要记录数据块的更改，还需要记录相关undo的信息。因此，由于delete操作需要记录全部列的undo信息，redo量往往大于update。

但是，考虑到supplemental log（附加日志）时，是什么情况呢？

SQL> select SUPPLEMENTAL_LOG_DATA_MIN,
2 SUPPLEMENTAL_LOG_DATA_PK,
3 SUPPLEMENTAL_LOG_DATA_UI,
4 SUPPLEMENTAL_LOG_DATA_ALL
5 from v$database
6 ;

SUPPLEME SUP SUP SUP
-------- --- --- ---
YES YES YES NO

SQL> create table test as select * from dba_objects;

表已创建。

SQL> set autot trace stat
SQL> delete from test;

已删除64968行。

统计信息
----------------------------------------------------------
0 recursive calls
72804 db block gets
922 consistent gets
0 physical reads
25260528 redo size
678 bytes sent via SQL*Net to client
585 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
64968 rows processed

SQL> rollback;

回退已完成。

SQL> update test set object_name=';

已更新64968行。

统计信息
----------------------------------------------------------
0 recursive calls
70704 db block gets
922 consistent gets
0 physical reads
29575832 redo size
680 bytes sent via SQL*Net to client
599 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
64968 rows processed

SQL> rollback;

回退已完成。

SQL> alter database drop supplemental log data;
alter database drop supplemental log data
*
第 1 行出现错误:
ORA-32589: 无法删除最小补充事件记录

SQL> alter database drop supplemental log data (primary key,unique index) column
s;

数据库已更改。

SQL> alter database drop supplemental log data;

数据库已更改。

SQL> select SUPPLEMENTAL_LOG_DATA_MIN,
2 SUPPLEMENTAL_LOG_DATA_PK,
3 SUPPLEMENTAL_LOG_DATA_UI,
4 SUPPLEMENTAL_LOG_DATA_ALL
5 from v$database;

SUPPLEME SUP SUP SUP
-------- --- --- ---
NO NO NO NO

SQL> set autot trace stat
SQL> delete from test;

已删除64968行。

统计信息
----------------------------------------------------------
0 recursive calls
72417 db block gets
922 consistent gets
0 physical reads
23954520 redo size
683 bytes sent via SQL*Net to client
585 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
64968 rows processed

SQL> rollback;

回退已完成。

SQL> update test set object_name=';

已更新64968行。

统计信息
----------------------------------------------------------
0 recursive calls
67563 db block gets
922 consistent gets
0 physical reads
17284752 redo size
683 bytes sent via SQL*Net to client
599 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
64968 rows processed

SQL> rollback;

回退已完成。

SQL> alter database add supplemental log data;

数据库已更改。

SQL> alter database add supplemental log data (primary key,unique index) columns
;

数据库已更改。

SQL> alter table test add constraint test_pk primary key(object_id);
alter table test add constraint test_pk primary key(object_id)
*
第 1 行出现错误:
ORA-01449: 列包含 NULL 值; 无法将其变更为 NOT NULL

SQL> delete test where object_id is null;

已删除2行。

SQL> commit;

提交完成。

SQL> alter table test add constraint test_pk primary key(object_id);

表已更改。

SQL> select SUPPLEMENTAL_LOG_DATA_MIN,
2 SUPPLEMENTAL_LOG_DATA_PK,
3 SUPPLEMENTAL_LOG_DATA_UI,
4 SUPPLEMENTAL_LOG_DATA_ALL
5 from v$database;

SUPPLEME SUP SUP SUP
-------- --- --- ---
YES YES YES NO

SQL> delete from test;

已删除64966行。

统计信息
----------------------------------------------------------
0 recursive calls
73779 db block gets
136 consistent gets
0 physical reads
26312052 redo size
683 bytes sent via SQL*Net to client
585 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
64966 rows processed

SQL> rollback;

回退已完成。

SQL> update test set object_name=';

已更新64966行。

统计信息
----------------------------------------------------------
90 recursive calls
68326 db block gets
972 consistent gets
0 physical reads
19907460 redo size
684 bytes sent via SQL*Net to client
599 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
64966 rows processed

SQL> rollback;

回退已完成。