【转】nologging与insert

转载自：http://hi.baidu.com/420350501/blog/item/46f1eed2892abc3107088b97.html

nologging与insert

2011-12-12 16:33

nologging是ORACLE提供的操作无日志选项，对于普通的INSERT 没有多少影响，   但如果INSERT的时候采用了/*+append*/，就会产生很少的日志

前提条件：非归档模式
测试如下（每次都是先删除表，再建表）
建表：
CREATE TABLE test1 AS SELECT * FROM dba_Objects WHERE 1>8 ;

表不作NOLOGGING处理：产生redo 8,697,536
SQL> SET ECHO OFF
SQL> SET VERIFY OFF
SQL> COLUMN VALUE NEW_VAL V
SQL> DEFINE S='&1'
SQL> SET AUTOTRACE OFF
Cannot SET AUTOTRACE
SQL> SELECT A.NAME,B.VALUE  FROM V$STATNAME A ,V$MYSTAT B
  2  WHERE A.STATISTIC#=B.STATISTIC#
  3  AND LOWER(A.NAME) =LOWER('&S');
 
NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                              1124
 
SQL> INSERT INTO test1 SELECT * FROM dba_objects ;
 
72220 rows inserted
 
SQL>
SQL> set echo off
SQL> set verify off ;
SQL> select distinct a.name,&v 执行前 ,b.value 执行后,to_char(b.value-&v,'999,999,999,999') 操作产生的redo
  2  from v$statname a ,v$mystat b
  3  where a.statistic#=b.statistic#
  4  and lower(a.name) =lower('&s');
 
NAME                                                                    执行前        执行后 操作产生的REDO
---------------------------------------------------------------- ---------- ---------- ----------------
redo size                                                              1124    8698660        8,697,536
 
SQL>
SQL> SET ECHO OFF
SQL> SET VERIFY OFF
SQL> COLUMN VALUE NEW_VAL V
SQL> DEFINE S='&1'
SQL> SET AUTOTRACE OFF
Cannot SET AUTOTRACE
SQL> SELECT A.NAME,B.VALUE  FROM V$STATNAME A ,V$MYSTAT B
  2  WHERE A.STATISTIC#=B.STATISTIC#
  3  AND LOWER(A.NAME) =LOWER('&S');
 
NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                           8698800
 
SQL> INSERT INTO test1 SELECT * FROM dba_objects nologging ;
 
72220 rows inserted
 
SQL>
SQL> set echo off
SQL> set verify off ;
SQL> select distinct a.name,&v 执行前 ,b.value 执行后,to_char(b.value-&v,'999,999,999,999') 操作产生的redo
  2  from v$statname a ,v$mystat b
  3  where a.statistic#=b.statistic#
  4  and lower(a.name) =lower('&s');
 
NAME                                                                    执行前        执行后 操作产生的REDO
---------------------------------------------------------------- ---------- ---------- ----------------
redo size                                                           8698800   17390924        8,692,124
 
对表进行nologging操作   产生redo 8,692,300  相对于不作处理，没有变化
SQL> alter table test1 nologging ;
 
Table altered
 
SQL>
SQL> SET ECHO OFF
SQL> SET VERIFY OFF
SQL> COLUMN VALUE NEW_VAL V
SQL> DEFINE S='&1'
SQL> SET AUTOTRACE OFF
Cannot SET AUTOTRACE
SQL> SELECT A.NAME,B.VALUE  FROM V$STATNAME A ,V$MYSTAT B
  2  WHERE A.STATISTIC#=B.STATISTIC#
  3  AND LOWER(A.NAME) =LOWER('&S');
 
NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                          17393168
 
SQL> INSERT INTO test1 SELECT * FROM dba_objects ;
 
72220 rows inserted
 
SQL>
SQL> set echo off
SQL> set verify off ;
SQL> select distinct a.name,&v 执行前 ,b.value 执行后,to_char(b.value-&v,'999,999,999,999') 操作产生的redo
  2  from v$statname a ,v$mystat b
  3  where a.statistic#=b.statistic#
  4  and lower(a.name) =lower('&s');
 
NAME                                                                    执行前        执行后 操作产生的REDO
---------------------------------------------------------------- ---------- ---------- ----------------
redo size                                                          17393168   26085468        8,692,300
 
 insert的时候，如果采用append  就会少很很多。 产生redo   66,600
SQL> SET ECHO OFF
SQL> SET VERIFY OFF
SQL> COLUMN VALUE NEW_VAL V
SQL> DEFINE S='&1'
SQL> SET AUTOTRACE OFF
Cannot SET AUTOTRACE
SQL> SELECT A.NAME,B.VALUE  FROM V$STATNAME A ,V$MYSTAT B
  2  WHERE A.STATISTIC#=B.STATISTIC#
  3  AND LOWER(A.NAME) =LOWER('&S');
 
NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                          26085608
 
SQL> INSERT/*+append*/ INTO test1 SELECT * FROM dba_objects ;
 
72220 rows inserted
 
SQL>
SQL> set echo off
SQL> set verify off ;
SQL> select distinct a.name,&v 执行前 ,b.value 执行后,to_char(b.value-&v,'999,999,999,999') 操作产生的redo
  2  from v$statname a ,v$mystat b
  3  where a.statistic#=b.statistic#
  4  and lower(a.name) =lower('&s');
 
NAME                                                                    执行前        执行后 操作产生的REDO
---------------------------------------------------------------- ---------- ---------- ----------------
redo size                                                          26085608   26152208           66,600

如果再将表更改为nologging ，然后再insert的时候APPEND   会产生更少的REDO 26,472
 
SQL> alter table test1 nologging ;
 
Table altered
 
SQL>
SQL> SET ECHO OFF
SQL> SET VERIFY OFF
SQL> COLUMN VALUE NEW_VAL V
SQL> DEFINE S='&1'
SQL> SET AUTOTRACE OFF
Cannot SET AUTOTRACE
SQL> SELECT A.NAME,B.VALUE  FROM V$STATNAME A ,V$MYSTAT B
  2  WHERE A.STATISTIC#=B.STATISTIC#
  3  AND LOWER(A.NAME) =LOWER('&S');
 
NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                          26162576
 
SQL> INSERT/*+append*/ INTO test1 SELECT * FROM dba_objects ;
 
72220 rows inserted
 
SQL>
SQL> set echo off
SQL> set verify off ;
SQL> select distinct a.name,&v 执行前 ,b.value 执行后,to_char(b.value-&v,'999,999,999,999') 操作产生的redo
  2  from v$statname a ,v$mystat b
  3  where a.statistic#=b.statistic#
  4  and lower(a.name) =lower('&s');
 
NAME                                                                    执行前        执行后 操作产生的REDO
---------------------------------------------------------------- ---------- ---------- ----------------
redo size                                                          26162576   26189048           26,472

测试条件：归档模式

以上结果大部分不会影响太大，只有append的时候会影响很大。

SQL> SET ECHO OFF
SQL> SET VERIFY OFF
SQL> COLUMN VALUE NEW_VAL V
SQL> DEFINE S='&1'
SQL> SET AUTOTRACE OFF
Cannot SET AUTOTRACE
SQL> SELECT A.NAME,B.VALUE  FROM V$STATNAME A ,V$MYSTAT B
  2  WHERE A.STATISTIC#=B.STATISTIC#
  3  AND LOWER(A.NAME) =LOWER('&S');
 
NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                           8736356
 
SQL> INSERT/*+append*/  INTO test1 a SELECT * FROM dba_objects ;
 
72220 rows inserted
 
SQL>
SQL> set echo off
SQL> set verify off ;
SQL> select distinct a.name,&v 执行前 ,b.value 执行后,to_char(b.value-&v,'999,999,999,999') 操作产生的redo
  2  from v$statname a ,v$mystat b
  3  where a.statistic#=b.statistic#
  4  and lower(a.name) =lower('&s');
 
NAME                                                                    执行前        执行后 操作产生的REDO
---------------------------------------------------------------- ---------- ---------- ----------------
redo size                                                           8736356   17555000        8,818,644
 这个区别也说明了，append只有在非归档模式下才会减少redo