表的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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/14359/viewspace-694068/