oracle 11 到 10 提示nologging,oracle nologging什么场景才能减少redo日志的产生

先说一下，经过自己的测试，发现oracle为了满足recover的要求，nologging实际上很少能够起作用，网上也有很多这方面的资料和测试，本文为亲身测试，发现在自己需要的场景中nologging完全失效，特做记录，并到官网上查询了一下什么操作在指定nologging的属性下会较少redo的产生。

表tlog

表tnolog

两个表的定义

create table tlog (id number,namevarchar2(1000));

create table tnolog (id number,namevarchar2(1000)) nologging;

生成归档量查询语句：

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';

执行如下过程查看归档量

begin

fori in 1..100000 loop

insert into tlogvalues(i,'sdljfalsdjflasjdflsajdflkjasdlfkjasld;kfj'||i||'asjdfljssjd;lfja;sd'||i);

commit;

endloop;

end;

/

执行前：

SQL> select * from sys.redo_size;

VALUE

----------

2063355264

执行后：

SQL> select * from sys.redo_size;

VALUE

----------

2119850312

redo生成：2119850312-2063355264=56495048

执行如下过程查看归档量

begin

fori in 1..100000 loop

insert into tnologvalues(i,'sdljfalsdjflasjdflsajdflkjasdlfkjasld;kfj'||i||'asjdfljssjd;lfja;sd'||i);

commit;

endloop;

end;

/

执行前：

SQL> select * from sys.redo_size;

VALUE

----------

2119850312

执行后：

SQL> select * from sys.redo_size;

VALUE

----------

2176210336

redo生成：2176210336-2119850312=56360024

结论：

归档模式生成：  56495048

非归档模式生成：56360024

没什么区别

select table_name,logging from dba_tableswhere table_name in ('TLOG','TNOLOG');

TABLE_NAME  LOGGING

TLOG    YES

TNOLOG  NO

那物化视图的刷新呢：

创建物化视图日志：

create materialized view log on tlog withsequence,rowid;

准备数据：

truncate table tlog;

truncate table tnolog;

begin

fori in 1..100000 loop

insert into tlogvalues(i,'sdljfalsdjflasjdflsajdflkjasdlfkjasld;kfj'||i||'asjdfljssjd;lfja;sd'||i);

commit;

endloop;

end;

/

查看mv log中的行数：

SQL> select count(*) from MLOG$_TLOG;

COUNT(*)

----------

100000

创建物化视图：

create materialized view  mv_tlog

BUILD DEFERRED

refresh fast on demand with rowid

as

select * from tlog;

create materialized view  mv_tnolog nologging

BUILD DEFERRED

refresh fast on demand with rowid

as

select * from tlog;

刷新查看归档量

刷新mv_tlog

SQL> select * from sys.redo_size;

VALUE

----------

2419178948

exec dbms_mview.refresh('MV_TLOG','C');

SQL> select * from sys.redo_size;

VALUE

----------

2440166340

生成归档量：    2440166340-2419178948=20987392

刷新mv_tnolog

SQL> select * from sys.redo_size;

VALUE

----------

2440166340

exec dbms_mview.refresh('MV_TNOLOG','C');

SQL> select * from sys.redo_size;

VALUE

----------

2461706932

生成归档量：    2461706932-2440166340=21540592

结论：

刷新logging的物化视图生成归档量        20987392

刷新nologging的物化视图生成归档量  21540592

nologging不少反多，真是奇怪。

终极结论：

像网上说的一样，nologging是在归档模式下的append下才会减少归档生成，这个属性有什么用途真是让人摸不到头脑。

/*+ append */

http://blog.csdn.net/linminqin/article/details/6602476

最后边有一个结论。

数据库模式

表模式

插入模式

REDO生成

ARCHIVELOG

LOGGING

APPEND

有REDO

NO APPEND

有REDO

NOLOGGING

APPEND

无REDO

NO APPEND

有REDO

NOARCHIVELOG

LOGGING

APPEND

无REDO

NO APPEND

有REDO

NOLOGGING

APPEND

无REDO

NO APPEND

有REDO

可惜物化视图的刷新不能使用append，一切幻想就成泡沫了。

哪些货语句可以减少redo，看mos上的说法：

9876

Doc ID

…

How to Avoid Generation of Redolog Entries (Doc ID    188691.1)

Modified:02-Mar-2013Type:HOWTO

***Checked for relevance on 25-Jul-2010*** ·goal: How to Avoid Generation of Redolog Entries · ·fact: Oracle Server - Enterprise Edition 8.1 · ·fact: Oracle Server - Enterprise Edition 9.0.1 · ·fact: Oracle Server - Enterprise Edition 9.2 · ·fact: Oracle Server - Enterprise Edition 10.1 · ·fact: Oracle Server - Enterprise Edition 10.2 · ·fact: Oracle Server - Enterprise Edition 11.1 · ·fact: Oracle Server - Enterprise Edition 11.2 · fix: The option NOLOGGING valid since Oracle8 can be used to avoid the redolog entries generation for a certain operation, that can be easily recovered without using the database recovery mechanism. The following operations can make use of nologging mode: - direct load (SQL*Loader) - direct-load INSERT - CREATE TABLE ... AS SELECT - CREATE INDEX - ALTER TABLE ... MOVE PARTITION - ALTER TABLE ... SPLIT PARTITION - ALTER INDEX ... SPLIT PARTITION - ALTER INDEX ... REBUILD - ALTER INDEX ... REBUILD PARTITION - INSERT /*+APPEND*/ INTO

AS    SELECT ... - INSERT, UPDATE, and DELETE on LOBs in NOCACHE NOLOGGING mode    stored out of line References: Oracle8i Concepts Manual Oracle8i SQL    Reference Oracle9i Concepts Manual Oracle9i SQL Reference Current version:    Oracle® Database SQL Language Reference 11g Release 2 (11.2)