有关nologging和append提高插入效率的研究

     那天接到一个事情，我们的数据库表空间已经快用完了，我们需要将一个3GB的表里的数据转储到历史表里去，3天干完。但是我们因为是给运营商服务的，所以白天是绝对不能做这个事情的，只能晚上干，这就要求我们必须尽可能的提高效率。有同事提议使用nologging和append提高效率，但是nologging和append是不是能够提高效率呢。我查询了官方文档，有这么一个描述：

 

   Conventional INSERT is the default in serial mode. In serial mode, direct path can be used only if you include the APPEND hint.

   Direct-path INSERT is the default in parallel mode. In parallel mode, conventional insert can be used only if you specify the NOAPPEND hint.

   In direct-path INSERT, data is appended to the end of the table, rather than using existing space currently allocated to the table. As a result, direct-path INSERT can be considerably faster than conventional INSERT.

 

 

     原来append模式的原理就是将数据直接插到表的最后，而不是插入到表的空闲空间中，这样从算法上讲，是一个很简单的算法，所以效率会提高不少。但是还是做个试验，验证一下吧。

     实验环境：windows7 x64，oracle11gR2，归档模式。

     实验一：append和nologging对insert的影响。

     1 建立试验用表。

      create table test1 as select * from dba_objects;

        2 记录现在系统中的redo size：

     select name, value from v$sysstat where name = 'redo size';

     现在系统的redo size为：915021984。

     3 普通模式插表，记录之后的redo size以及时间：

     insert into test1 (select * from dba_objects);

     commit;

     现在的redo size为：936621172

     耗用时间为：1.264秒

     这个操作产生的redo为：21599188

     4 drop掉试验表，重建该表，使用nologging hint，记录前后的redo size：

     insert /*+ nologging*/ into test1 (select * from dba_objects);

     commit;

         插入之前的redo size：953810184

    插入之后的redo size：962152616

    耗时：1.092秒

    这个操作产生的redo为：8342432。

    5 drop该表，重建之。以append hint插入：

    插入之前的redo size：997153368

    插入之后的redo size：1005655116

    耗时：1.014秒

    该操作产生的redo：8501748

    6 drop该表，重建之。将表调整为nologging模式，以append hint插入：

    alter table test1 nologging;

    insert /*+ append*/ into test1 (select * from dba_objects);

    commit;

    插入之前的redo size：988037948

    插入之后的redo size：988156952

    操作耗时：1.029秒

    这个操作产生的redo为：119004。

    实验一的总结：从第四步可以很明显的看出来，使用nologging hint插表，效率可以得到很大的提升，我这个试验表比较小，在时间上还看不出明显的区别，但是如果放在生产环境上，从redo size的产生情况就可以看出，nologging模式对效率的提升应该是非常可观的。从第五步就能看出，使用append hint也可以很好的提升效率。但是，如果nologging和append一起使用，效果更好，产生的redo比之前述两种更是少了一个数量级，比直接插入少了两个数量级。不过，在实际的生产环境中，表的模式不能随意更改，因此有时候也只能使用nologging模式来做最可能的性能优化。

    任何性能的提升总要有一定的牺牲。如果append hint能很好的提高效率，为什么oracle不会直接默认就选择它？这个问题依我浅见，应该是担心产生磁盘碎片。虽说以后的插入会使用现有的空闲空间，但是我估计这种操作会产生碎片的概率要远远高于普通插入。具体的资料我还没有找到，如果找到了，一定即使在这里说。

    如果各位能够给我讲解一二，小弟不胜荣幸。