測試如下：
首先，我們可以建立這樣一個視圖給所有使用者用:create or replace view redo_size
as
select value
from v$mystat, v$statname
where v$mystat.statistic# = v$statname.statistic#
and v$statname.name = 'redo size';
表的Nologging屬性是否生效跟資料庫的運作模式有關
a.在歸檔模式下idle> startup
ORACLE instance started.
Total System Global Area 176160768 bytes
Fixed Size 1218388 bytes
Variable Size 88082604 bytes
Database Buffers 83886080 bytes
Redo Buffers 2973696 bytes
Database mounted.
Database opened.
idle> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 38
Next log sequence to archive 40
Current log sequence 40
一、表具有nologging屬性idle> create table test nologging as select * from dba_objects where 1=0;
Table created.
idle> select * from redo_size;
VALUE
----------
84968
idle> insert into test select * from dba_objects;
50387 rows created.
idle> select * from redo_size;
VALUE
----------
5774692
idle> insert into test select * from dba_objects;
50387 rows created.
idle> select * from redo_size;
VALUE
----------
5784492
idle> select (5784492 -5774692) redo_append,(5774692 - 84968) redo from dual;
REDO_APPEND REDO
----------- ----------
9800 5689724
我們發現在archive log模式下，對具有nologging屬性表的insert append操作的才能減少redo
的生成。
idle> drop table test;
Table dropped.
二、表不具有nologging屬性
idle> create table test as select * from dba_objects where 1=0;
Table created.
idle> select * from redo_size;
VALUE
----------
5831196
idle> insert into test select * from dba_objects;
50424 rows created.
idle> select * from redo_size;
VALUE
----------
11522108
idle> insert into test select * from dba_objects;
50424 rows created.
idle> select * from redo_size;
VALUE
----------
17241076
idle> select (17241076 -11522108) redo_append,(11522108 - 5831196) redo from dual;
REDO_APPEND REDO
----------- ----------
5718968 5690912
對不具有nologging屬性的表的insert操作、insert append操作都産生redo;
b.在非歸檔模式下
一、表不具有nologging屬性[email protected]> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 41
Current log sequence 43
[email protected]> create table test as select * from dba_objects where 1=0;
Table created.
[email protected]> select * from redo_size;
VALUE
----------
45688
[email protected]> insert into test select * from dba_objects;
50498 rows created.
[email protected]> select * from redo_size;
VALUE
----------
5754616
[email protected]> insert into test select * from dba_objects;
50498 rows created.
[email protected]> select * from redo_size;
VALUE
----------
5764336
[email protected]> select (5764336 -5754616) redo_append,(5754616 -45688) redo from dual;
REDO_APPEND REDO
----------- ----------
9720 5708928
我們看到在Noarchivelog模式下,對于不具有nologging屬性的表的insert append操作隻産生少
量redo
二、表具有nologging屬性[email protected]> drop table test;
Table dropped.
[email protected]> create table test nologging as select * from dba_objects where 1=0;
Table created.
[email protected]> select * from redo_size;
VALUE
----------
71680
[email protected]> insert into test select * from dba_objects;
50498 rows created.
[email protected]> select * from redo_size;
VALUE
----------
5772936
[email protected]> insert into test select * from dba_objects;
50498 rows created.
[email protected]> select * from redo_size;
VALUE
----------
5782640
[email protected]> select (5782640 -5772936) redo_append,(5772936 -71680) redo from dual;
REDO_APPEND REDO
----------- ----------
9704 5701256
同樣隻有insert append操作才能減少redo的生成.
結論：通過測試我們知道,表的Nologging屬性隻在很少情況下生效；通常,DML操作總是要生成redo的；
資料庫運作在非歸檔日志模式下：
對具有nologging屬性的表和不具有nologging屬性的表的普通insert操作都産生redo;
對具有nologging屬性的表和不具有nologging屬性的表的insert append操作都産生少量redo;
資料庫運作在歸檔日志模式下：
對具有nologging屬性的表和不具有nologging屬性的表的insert操作都産生redo;
對不具有nologging屬性的表的insert append操作都産生redo;
對具有nologging屬性的表的insert append操作隻産生少量redo;
不管表是否具有nologging屬性，direct insert 都不會對資料産生 UNDO