關於delete還是update會產生更多日誌的問題

論壇上有兄弟問http://www.itpub.net/thread-1338094-1-1.html

對於redo而言，不僅僅需要記錄資料塊的更改，還需要記錄相關undo的資訊。因此，由於delete操作需要記錄全部列的undo資訊，redo量往往大於update。

但是，考慮到supplemental log（附加日誌）時，是什麼情況呢？

[@more@]

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;

回退已完成。