表的nologging和logging屬性對資料庫redo資料生成的影響

測試如下：
首先，我們可以建立這樣一個檢視給所有使用者用:
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 /*+ append */ 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 /*+ append */ 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屬性
sys@EXAM> 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
sys@EXAM> create table test as select * from dba_objects where 1=0;

Table created.

sys@EXAM> select * from redo_size;

     VALUE
----------
     45688
sys@EXAM> insert into test select * from dba_objects;

50498 rows created.

sys@EXAM> select * from redo_size;

     VALUE
----------
   5754616

sys@EXAM> insert /*+ append */ into test select * from dba_objects;

50498 rows created.

sys@EXAM> select * from redo_size;

     VALUE
----------
   5764336

sys@EXAM> select (5764336 -5754616) redo_append,(5754616 -45688) redo from dual;

REDO_APPEND       REDO
----------- ----------
       9720    5708928

我們看到在Noarchivelog模式下,對於不具有nologging屬性的表的insert append操作只產生少

量redo
二、表具有nologging屬性
sys@EXAM> drop table test;

Table dropped.

sys@EXAM> create table test nologging as select * from dba_objects where 1=0;

Table created.

sys@EXAM> select * from redo_size;

     VALUE
----------
     71680

sys@EXAM>  insert into test select * from dba_objects;

50498 rows created.

sys@EXAM> select * from redo_size;

     VALUE
----------
   5772936

sys@EXAM> insert /*+ append */ into test select * from dba_objects;

50498 rows created.

sys@EXAM> select * from redo_size;

     VALUE
----------
   5782640

sys@EXAM> 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

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/14359/viewspace-694068/，如需轉載，請註明出處，否則將追究法律責任。