根据Thoms kyte所讲，只有在数据库在noarchivedlog方式，或者在表为nologging的条件下，直接加载才会减少redo size，本人在11g下实验，发现事实还有一个重要的条件，就是数据库必须为非no force logging，废话自不必说，请看试验数据:
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
PL/SQL Release 11.1.0.7.0 - Production
CORE 11.1.0.7.0 Production
TNS for 32-bit Windows: Version 11.1.0.7.0 - Production
NLSRTL Version 11.1.0.7.0 - Production
SQL> select force_logging from v$database;
FOR
---
YES
首先，我们在数据库为force logging，且表为logging的情况下试验
SQL> set autotrace traceonly statistics
SQL> insert into test select * from all_objects;
已创建67935行。
统计信息
----------------------------------------------------------
3058 recursive calls
11066 db block gets
94085 consistent gets
3697 physical reads
8214548 redo size
681 bytes sent via SQL*Net to client
611 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1467 sorts (memory)
0 sorts (disk)
67935 rows processed
SQL> insert /*+ append */ into test select * from all_objects;
已创建67935行。
统计信息
----------------------------------------------------------
615 recursive calls
1327 db block gets
91913 consistent gets
1018 physical reads
8253892 redo size
665 bytes sent via SQL*Net to client
625 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1454 sorts (memory)
0 sorts (disk)
67935 rows processed
我们可以看到两者并没有显著变化。此时当我们把表变为nologging 再来试验(数据库仍为force logging)
SQL> insert into test select * from all_objects;
已创建67974行。
统计信息
----------------------------------------------------------
924 recursive calls
10238 db block gets
94697 consistent gets
3730 physical reads
8139548 redo size
680 bytes sent via SQL*Net to client
611 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1463 sorts (memory)
0 sorts (disk)
67974 rows processed
SQL> insert /*+ append */ into test select * from all_objects;
已创建67974行。
统计信息
----------------------------------------------------------
703 recursive calls
1388 db block gets
92859 consistent gets
1023 physical reads
8274032 redo size
666 bytes sent via SQL*Net to client
625 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1458 sorts (memory)
0 sorts (disk)
67974 rows processed
可以看到两者并没有显著变化，为什么呢？我想当数据库为force logging时，表为nologging会失效，所以直接加载仍会建立重做日志。
所以此时，我把数据库变为no force logging再来试验
SQL> alter database no force logging;
数据库已更改。
SQL> insert into test select * from all_objects;
已创建67974行。
统计信息
----------------------------------------------------------
660 recursive calls
10258 db block gets
94639 consistent gets
3726 physical reads
8140264 redo size
682 bytes sent via SQL*Net to client
611 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1458 sorts (memory)
0 sorts (disk)
67974 rows processed
SQL> insert /*+ append */ into test select * from all_objects;
已创建67974行。
统计信息
----------------------------------------------------------
630 recursive calls
1362 db block gets
92414 consistent gets
1021 physical reads
28744 redo size
666 bytes sent via SQL*Net to client
625 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1456 sorts (memory)
0 sorts (disk)
67974 rows processed
可以看到redo size急剧减少，可以证明正是由于数据库的force logging导致表上的nologging失效所致。
下面再来看看，当数据库设置为unarchivelog下时，又会是怎样呢？
SQL>alter database force logging;
SQL> shutdown immediate;
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup mount;
ORACLE 例程已经启动。
Total System Global Area 535662592 bytes
Fixed Size 1348508 bytes
Variable Size 251661412 bytes
Database Buffers 276824064 bytes
Redo Buffers 5828608 bytes
数据库装载完毕。
SQL> alter database flashback off;
数据库已更改。
SQL> alter database noarchivelog;
数据库已更改。
SQL> set autotrace traceonly statistics;
SQL> insert into test select * from all_objects;
已创建68207行。
统计信息
----------------------------------------------------------
1181 recursive calls
5868 db block gets
44937 consistent gets
0 physical reads
8193496 redo size
669 bytes sent via SQL*Net to client
611 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
768 sorts (memory)
0 sorts (disk)
68207 rows processed
SQL> insert /*+ append */ into test select * from all_objects;
已创建68207行。
统计信息
----------------------------------------------------------
432 recursive calls
1106 db block gets
42729 consistent gets
0 physical reads
14704 redo size
659 bytes sent via SQL*Net to client
625 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
768 sorts (memory)
0 sorts (disk)
68207 rows processed
可以看到当数据库为非归档模式的时候，直接加载会大幅度的减少redo的生成。
结下来我们在此条件下把test设置为nologging在来看看试验结果
SQL> alter table test nologging;
表已更改。
SQL> insert into test select * from all_objects;
已创建68207行。
统计信息
----------------------------------------------------------
661 recursive calls
5649 db block gets
44886 consistent gets
0 physical reads
8167952 redo size
675 bytes sent via SQL*Net to client
611 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
772 sorts (memory)
0 sorts (disk)
68207 rows processed
SQL> insert /*+ append */ into test select * from all_objects;
已创建68207行。
统计信息
----------------------------------------------------------
432 recursive calls
1106 db block gets
42729 consistent gets
0 physical reads
14904 redo size
658 bytes sent via SQL*Net to client
625 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
768 sorts (memory)
0 sorts (disk)
68207 rows processed
可以看到生成的redo在非归档模式下，直接加载两次结果一致。说明表为 logging + /*+ append */ 与表为nologging+/*+ append */并没有区别。
在非归档模式下，如果数据库为force nologging会不会影响redo呢？
SQL> alter database no force logging;
数据库已更改。
SQL> select force_logging from v$database;
FOR
---
NO
SQL> set autotrace traceonly statistics
SQL> insert into test select * from all_objects;
已创建68207行。
统计信息
----------------------------------------------------------
395 recursive calls
5643 db block gets
44815 consistent gets
0 physical reads
8167152 redo size
677 bytes sent via SQL*Net to client
611 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
768 sorts (memory)
0 sorts (disk)
68207 rows processed
SQL> insert /*+ append */ into test select * from all_objects;
已创建68207行。
统计信息
----------------------------------------------------------
370 recursive calls
1093 db block gets
42711 consistent gets
0 physical reads
13164 redo size
661 bytes sent via SQL*Net to client
625 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
768 sorts (memory)
0 sorts (disk)
68207 rows processed
可以看到，redo的影响并不是特别大。
由此可以得出如下结论:
1.archivelog+force logging+/*+ append */+table logging并不会使redo减少，直接加载仍然会生成大量的redo
2.archivelog+force logging+/*+ append */+table nologging也不会时redo减少，因为数据库的force logging会使table上的nologging失效，所以直接加载仍然会产生大量的redo
3.archivelog+no force logging+/*+ append */+table logging会大量减少redo
4.archivelog+no force logging+/*+ append */+table nologging与上一个产生相同的redo
5.unchivelog +force logging +/*+ append */+table logging会大量减少redo
6,unchivelog +force logging +/*+ append */+table nologging产生上一步一样的redo
7.unchivelog+no force logging+/*+ append */+table logging产生的redo会大量减少(没有贴出来)
8.unchivelog+no force logging +/*+ append */+table nologging产生的redo会大量减少.
