直接加载和redo！

首先有t1和t2两个表，t1里面没有记录，t2里面有大约40万条记录：

SQL> select count(*) from t1;

  COUNT(*)
----------
         0

SQL> select count(*) from t2;

  COUNT(*)
----------
    402344

下面是表是否记录redo的操作：

SQL> select table_name,logging from user_tables where table_name = 'T1';

TABLE_NAME                     LOG
------------------------------ ---
T1                             YES

SQL> alter table t1 nologging;

表已更改。

SQL> select table_name,logging from user_tables where table_name = 'T1';

TABLE_NAME                     LOG
------------------------------ ---
T1                             NO

SQL> alter table t1 logging;

表已更改。

SQL> select table_name,logging from user_tables where table_name = 'T1';

TABLE_NAME                     LOG
------------------------------ ---
T1                             YES

可以看见在logging模式下，普通加载和直接加载redo size大小差别不是很大！

SQL> alter table t1 logging;

表已更改。

SQL> set autotrace trace stat;
SQL> insert into t1 select * from t2;

已创建402344行。


统计信息
----------------------------------------------------------
       2783  recursive calls
      50954  db block gets
      12904  consistent gets
       5560  physical reads
   45313632  redo size
        670  bytes sent via SQL*Net to client
        564  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          6  sorts (memory)
          0  sorts (disk)
     402344  rows processed

SQL> rollback;

回退已完成。

SQL> insert /*+ append */ into t1 select * from t2;

已创建402344行。


统计信息
----------------------------------------------------------
       1016  recursive calls
       6366  db block gets
       5956  consistent gets
       5608  physical reads
   45712692  redo size
        655  bytes sent via SQL*Net to client
        578  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
     402344  rows processed

可以看见在nologging状态下面，差别就出来了吧！

SQL> set autotrace trace stat;
SQL> alter table t1 nologging;

表已更改。

SQL> insert into t1 select * from t2;

已创建402344行。


统计信息
----------------------------------------------------------
        180  recursive calls
      41450  db block gets
      16625  consistent gets
       9405  physical reads
   44639472  redo size
        672  bytes sent via SQL*Net to client
        564  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          6  sorts (memory)
          0  sorts (disk)
     402344  rows processed

SQL> rollback;

回退已完成。

SQL> insert /*+ append */ into t1 select * from t2;

已创建402344行。


统计信息
----------------------------------------------------------
          4  recursive calls
       5625  db block gets
       5610  consistent gets
       5632  physical reads
      10572  redo size
        656  bytes sent via SQL*Net to client
        578  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
     402344  rows processed