mysql 有 nologging_表的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