[转载]NOLOGGING&APPEND

原文地址：NOLOGGING&APPEND作者：dbafans

在工作中我们一直在为如何优化SQL努力着；为了提高数据INSERT效率，NOLOGGING与APPEND被频繁使用，但是他们什么时候真正生效，对于我来说一直很模糊；今天在群里有人提出这个问题，很多人也是一知半解；虽然网上有不少大师已经总结出了结论，所谓纸上得来终觉浅，借此机会，决定亲自实践研究一番。
1.非归档模式
1.1.表LOGGING属性为:TURE

SQL> archive log list
数据库日志模式             非存档模式
自动存档             禁用
存档终点            USE_DB_RECOVERY_FILE_DEST
最早的联机日志序列     1
当前日志序列           2
SQL> select * from crm.redo_size;
     VALUE                                                                      
----------                                                                      
   5805956                                                                     
SQL> create table crm.test as select * from dba_objects;
表已创建。
SQL> select * from crm.redo_size;
     VALUE                                                                      
----------                                                                      
   5885568                                                                     
SQL> select 5885568-5805956 t1 from dual;
        T1                                                                      
----------                                                                      
     79612                                                                     
SQL> insert into crm.test select * from dba_objects;
已创建49748行。
SQL> select * from crm.redo_size;
     VALUE                                                                      
----------                                                                      
  11458948                                                                     
SQL> select 11458948-5885568 t2 from dual;
        T2                                                                      
----------                                                                      
   5573380                                                                     
SQL> insert into crm.test select * from dba_objects;
已创建49748行。
SQL> select * from crm.redo_size;
     VALUE                                                                      
----------                                                                      
  11478864                                                                     
SQL> select 11478864-11458948 t3 from dual;
        T3                                                                      
----------                                                                      
     19916

1.2.表LOGGING属性为:FALSE

SQL> archive log list;
数据库日志模式             非存档模式
自动存档             禁用
存档终点            USE_DB_RECOVERY_FILE_DEST
最早的联机日志序列     1
当前日志序列           3
SQL> select * from crm.redo_size;
     VALUE                                                                      
----------                                                                      
     17780                                                                     
SQL> create table crm.test nologging as select * from dba_objects;
表已创建。
SQL> select * from crm.redo_size;
     VALUE                                                                      
----------                                                                      
     97680                                                                     
SQL> select 97680-17780 t10 from dual;
       T10                                                                      
----------                                                                      
     79900                                                                     
SQL> insert into crm.test select * from dba_objects;
已创建49751行。
SQL> select * from crm.redo_size;
     VALUE                                                                      
----------                                                                      
   5670484                                                                     
SQL> select 5670484-97680 t11 from dual;
       T11                                                                      
----------                                                                      
   5572804                                                                     
SQL> insert into crm.test select * from dba_objects;
已创建49751行。
SQL> select * from crm.redo_size;
     VALUE                                                                      
----------                                                                      
   5690412                                                                     
SQL> select 5690412-5670484 t12 from dual;
       T12                                                                      
----------                                                                      
     19928

2.归档模式
2.1.表LOGGING属性为:TRUE

SQL> shutdown immediate
ORA-01097: 无法在事务处理过程中关闭 - 请首先提交或回退
SQL> commit;
提交完成。
SQL> shutdown immediate
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup mount
ORACLE 例程已经启动。
Total System Global Area  285212672 bytes                                      
Fixed Size                  1248576 bytes                                      
Variable Size             100664000 bytes                                      
Database Buffers          180355072 bytes                                      
Redo Buffers                2945024 bytes                                      
数据库装载完毕。
SQL> alter database archivelog;
数据库已更改。
SQL> alter database open;
数据库已更改。
SQL> archive log list;
数据库日志模式            存档模式
自动存档             启用
存档终点            USE_DB_RECOVERY_FILE_DEST
最早的联机日志序列     1
下一个存档日志序列   2
当前日志序列           2
SQL> select * from crm.redo_size;
     VALUE                                                                      
----------                                                                      
     17848                                                                     
SQL> drop table crm.test;
表已删除。
SQL> select * from crm.redo_size;
     VALUE                                                                      
----------                                                                      
     22580                                                                     
SQL> create table crm.test as select * from dba_objects;
表已创建。
SQL> select * from crm.redo_size;
     VALUE                                                                      
----------                                                                      
   5745552                                                                     
SQL> select 5745552-22580 t4 from dual;
        T4                                                                      
----------                                                                      
   5722972                                                                     
SQL> insert into crm.test select * from dba_objects;
已创建49749行。
SQL> select * from crm.redo_size;
     VALUE                                                                      
----------                                                                      
  11318264                                                                     
SQL> select 11318264-5745552 t5 from dual;
        T5                                                                      
----------                                                                      
   5572712                                                                     
SQL> insert into crm.test select * from dba_objects;
已创建49749行。
SQL> select * from crm.redo_size;
     VALUE                                                                      
----------                                                                      
  16964808                                                                     
SQL> select 16964808-11318264 t6 from dual;
        T6                                                                      
----------                                                                      
   5646544

 2.2.表LOGGING属性为:FALSE

SQL> select * from crm.redo_size;
     VALUE                                                                      
----------                                                                      
  16974024                                                                     
SQL> create table crm.test nologging as select * from dba_objects;
表已创建。
SQL> select * from crm.redo_size;
     VALUE                                                                      
----------                                                                      
  17071616                                                                     
SQL> select 17071616-16974024 t7 from dual;
        T7                                                                      
----------                                                                      
     97592                                                                     
SQL> insert into crm.test select * from dba_objects;
已创建49750行。
SQL> select * from crm.redo_size;
     VALUE                                                                      
----------                                                                      
  22644348                                                                     
SQL> select 22644348-17071616 t8 from dual;
        T8                                                                      
----------                                                                      
   5572732                                                                     
SQL> insert into crm.test select * from dba_objects;
已创建49750行。
SQL> select * from crm.redo_size;
     VALUE                                                                      
----------                                                                      
  22664232                                                                     
SQL> select 22664232-22644348 from dual;
22664232-22644348                                                              
-----------------                                                              
            19884                                                                
SQL> select 22664232-22644348 t9  from dual;
        T9                                                                      
----------                                                                      
     19884

3.根据以上试验整理得到试验结果:

4.根据试验结果得出初步结论
①CREATE TABLE在非归档模式下表的LOGING属性对产生REDO大小无太大影响；相反，在归档模式下,   LOGING在TURE与FALSE下产生REDO大小相差很大,显然FALSE下效率高；
②INSERT INTO 在是否归档下及是否LOGGING下产生REDO大小相差无几；
③在非归档及LOGGING为FALSE下，产生REDO最小,效率最高.在归档模式下，表LOGGING为TURE时，与普通INSERT INTO 相差无几；
④⑤⑦与⑥比较:在非归档和归档NOLOGGING下，三种操作中，效率最高,CREATE TABLE次之,普通INSERT最慢。而归档LOGGING下，三种操作相差无几；

5.最终结论
在非归档模式下,无论表是否LOGGING,APPEND都生效，在数据倒换及迁移时，尽量采用APPEND或者CREATE TABLE AS方式;在归档模式下,只有表LOGGING为FALSE时才生效,在数据倒换及迁移时,可以先将表设置为NOLOGGING,并采用APPEND(有风险!完成后需进行全备);