此时最好的解决办法是insert数据时, 将表设置为nologging，速度是比较快的。
这个时候oracle只会生成最低限度的必须的redo log，而没有一点undo信息。如果有可能将index也删除，重建。
先看看实验结果：
archivelog
|
操作类型 |
Redo大小（byte） |
|
insert |
10896388 |
|
append insert |
11143288 |
|
nologging insert |
11088572 |
|
nologging+append |
29404 |
|
操作类型 |
Redo大小（byte） |
|
insert |
11105144 |
|
append insert |
29404 |
|
nologging insert |
10894408 |
|
nologging+append |
4964 |
数据库版本：
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
首先确认数据库处于非归档模式
SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination /u01/app/oracle/product/11.2.0/db_1/dbs/arch
Oldest online log sequence 692
Current log sequence 695
一、如果表采用默认的方式，也就是记录日志的方式，不管你是否使用insert into， oracle产生的日志都会很多
SQL> conn scott
Enter password:
Connected.
SQL> create table tj as select * from dba_objects where 1=2;
SQL> select count(*) from tj;
COUNT(*)
----------
0
SQL> select table_name,logging from user_tables where table_name='TJ';
TABLE_NAME LOG
------------------------------ ---
TJ YES
通过autotrace统计redo生成
SQL> set autotrace trace stat
1.直接insert插入
SQL> insert into tj select * from dba_objects;
95167 rows created.
Statistics
----------------------------------------------------------
188 recursive calls
12890 db block gets
3592 consistent gets
10 physical reads
11105144 redo size
837 bytes sent via SQL*Net to client
793 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
12 sorts (memory)
0 sorts (disk)
95167 rows processed
SQL> rollback;
Rollback complete.
2.采用append 的方式
SQL> insert /*+ append */ into tj select * from dba_objects;
95167 rows created.
Statistics
----------------------------------------------------------
37 recursive calls
1715 db block gets
1240 consistent gets
0 physical reads
29404 redo size
823 bytes sent via SQL*Net to client
808 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
95167 rows processed
SQL> rollback;
Rollback complete.
通过以上1和2两种不同方式的比较，我们发现产生的日志量减少很多11105144 redo size
29404 redo size
二、修改表为不记录日志，这个时候insert into就会体现出他的优势
1. 直接insert插入
SQL> alter table tj nologging;
Table altered.
SQL> insert into tj select * from dba_objects;
95167 rows created.
Statistics
----------------------------------------------------------
40 recursive calls
10145 db block gets
3957 consistent gets
0 physical reads
10894408 redo size
840 bytes sent via SQL*Net to client
793 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
7 sorts (memory)
0 sorts (disk)
95167 rows processed
SQL> rollback;
Rollback complete.
2.采用append 的方式
SQL> insert /*+ append */ into tj select * from dba_objects;
95167 rows created.
Statistics
----------------------------------------------------------
0 recursive calls
1419 db block gets
1204 consistent gets
0 physical reads
4964 redo size
826 bytes sent via SQL*Net to client
808 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
95167 rows processed
通过以上1和2两种不同方式的比较，我们发现产生的日志量APPEND的方式明显会少，10894408 redo size
4964 redo size
根据上面的实验我们可以发现，为了通过减少REDO而提高语句的性能，我们要满足两个条件1.表NOLOGGING 2.在语句中使用APPEND提示。
当然我们这里只是在强调性能，作为一个DBA，在性能和安全之间一定要做一个平衡，当你选择了NOLOGGING的时候，由于表是不记录日志的，那如果数据库崩溃，这些数据是不能被恢复的。
noarchivelog:
insert: 11105144 redo size
append: 29404 redo size
nologging: 10894408 redo size
nologging+append: 4964 redo size
下面是一个小的例子，当我们把一个表设置成NOLOGGING模式的时候，通过合适的操作，可以让oracle产生很少的REDO。
首先确认数据库处于归档模式
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/product/11.2.0/db_1/dbs/arch
Oldest online log sequence 692
Next log sequence to archive 695
Current log sequence 695
一、如果表采用默认的方式，也就是记录日志的方式，不管你是否使用insert into， oracle产生的日志都会很多
SQL> conn scott
Enter password:
Connected.
SQL> create table tj as select * from dba_objects where 1=2;
Table created.
SQL> select count(*) from tj;
COUNT(*)
----------
0
SQL> select table_name,logging from user_tables where table_name='TJ';
TABLE_NAME LOG
------------------------------ ---
TJ YES
通过autotrace统计redo生成
SQL> set autotrace trace stat
1.直接insert方式插入
SQL> insert into tj select * from dba_objects;
95167 rows created.
Statistics
----------------------------------------------------------
402 recursive calls
10107 db block gets
4200 consistent gets
19 physical reads
10896388 redo size
835 bytes sent via SQL*Net to client
793 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
33 sorts (memory)
0 sorts (disk)
95167 rows processed
SQL> rollback;
Rollback complete.
2.采用append 提示的方式
SQL> insert /*+ append */ into tj select * from dba_objects;
95167 rows created.
Statistics
----------------------------------------------------------
15 recursive calls
1395 db block gets
1208 consistent gets
22 physical reads
11143288 redo size
823 bytes sent via SQL*Net to client
808 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
95167 rows processed
SQL> rollback;
Rollback complete.
通过以上1和2两种不同方式的比较，我们发现产生的日志量没有减少10896388 redo size
11143288 redo size
二、修改表为不记录日志，这个时候insert into就会体现出他的优势
1.不采用append 提示
SQL> alter table tj nologging;
Table altered.
SQL> select table_name,logging from user_tables where table_name='TJ';
TABLE_NAME LOG
------------------------------ ---
TJ NO
SQL> insert into tj select * from dba_objects;
95167 rows created.
Statistics
----------------------------------------------------------
50 recursive calls
12847 db block gets
3423 consistent gets
3 physical reads
11088572 redo size
836 bytes sent via SQL*Net to client
793 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
95167 rows processed
SQL> rollback;
Rollback complete.
2.采用append 的方式
SQL> insert /*+ append */ into tj select * from dba_objects;
95167 rows created.
Statistics
----------------------------------------------------------
37 recursive calls
1714 db block gets
1240 consistent gets
0 physical reads
29404 redo size
824 bytes sent via SQL*Net to client
808 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
95167 rows processed
通过以上1和2两种不同方式的比较，我们发现产生的日志量APPEND的方式明显会少，11088572 redo size
29404 redo size
根据上面的实验我们可以发现，为了通过减少REDO而提高语句的性能，我们要满足两个条件1.表NOLOGGING 2.在语句中使用APPEND提示。
当然我们这里只是在强调性能，作为一个DBA，在性能和安全之间一定要做一个平衡，当你选择了NOLOGGING的时候，由于表是不记录日志的，那如果数据库崩溃，这些数据是不能被恢复的。