结论
直接先把结论写在下面：
一、插入数据：
1、非归档模式下
create insert append append+nologging nologging
---------- ---------- ---------- ---------------- ----------
110276 759944 2632 1004 752772结论：append+nologging 的日志量最小。
2、归档模式下
create insert append append+nologging nologging
---------- ---------- ---------- ---------------- ----------
874812 760320 784092 782508 752816结论：差别不大，nologging 最小。用不用差不多。
(后来验证，先alter table nologging 然后再insert /+ append/，能有效降低REDO大小，忘记记录了，大家可以自己做个实验验证一下)
二、创建索引（非归档）
SQL> select (930388-656600) nologging ,(625448-349940) logging from dual;
NOLOGGING LOGGING
---------- ----------
273788 275508nologging 会快一点，并不多。日志差不多。
三、表空间nologging（非归档）
SQL> select (886316-99060),(1699944-912444) from dual;
(886316-99060) (1699944-912444)
-------------- ----------------
787256 787500nologging没有作用
实验内容：
Release 11.2.0.1.0 Production on Thu Jul 14 18:11:12 20161、非归档模式
SQL> select a.name,b.value
from v$statname a,v$mystat b
where a.statistic#=b.statistic# and a.name='redo size'; 2 3
NAME VALUE
---------------------------------------------------------------- ----------
redo size 0
SQL> create table testredo as select * from dba_tables;
?????
SQL> select a.name,b.value
2 from v$statname a,v$mystat b
3 where a.statistic#=b.statistic# and a.name='redo size';
NAME VALUE
---------------------------------------------------------------- ----------
redo size 110276insert into testredo select * from dba_tables;
commit;
SQL> select a.name,b.value
2 from v$statname a,v$mystat b
3 where a.statistic#=b.statistic# and a.name='redo size';
NAME VALUE
---------------------------------------------------------------- ----------
redo size 870220
insert /*+ append*/ into testredo select * from dba_tables;
commit;
SQL> select a.name,b.value
2 from v$statname a,v$mystat b
3 where a.statistic#=b.statistic# and a.name='redo size';
NAME VALUE
---------------------------------------------------------------- ----------
redo size 872852
insert /*+ append nologging */ into testredo select * from dba_tables;
commit;
SQL> select a.name,b.value
2 from v$statname a,v$mystat b
3 where a.statistic#=b.statistic# and a.name='redo size';
NAME VALUE
---------------------------------------------------------------- ----------
redo size 873856
insert /*+ nologging */ into testredo select * from dba_tables;
commit;
SQL> select a.name,b.value
2 from v$statname a,v$mystat b
3 where a.statistic#=b.statistic# and a.name='redo size';
NAME VALUE
---------------------------------------------------------------- ----------
redo size 1626628
select (110276-0) "create" ,(870220-110276) "insert",(872852-870220) "append", (873856-872852) "append nologging",(1626628-873856) "nologging" from dual;
create insert append append nologging nologging
---------- ---------- ---------- ---------------- ----------
110276 759944 2632 1004 752772结论：append nologging 最小。
2、归档模式
SQL> select a.name,b.value
2 from v$statname a,v$mystat b
3 where a.statistic#=b.statistic# and a.name='redo size';
NAME VALUE
---------------------------------------------------------------- ----------
redo size 0
SQL> create table testredo as select * from dba_tables;
?????
SQL> select a.name,b.value
2 from v$statname a,v$mystat b
3 where a.statistic#=b.statistic# and a.name='redo size';
NAME VALUE
---------------------------------------------------------------- ----------
redo size 874812SQL> insert into testredo select * from dba_tables;
commit;
???2729??
SQL>
?????
SQL> select a.name,b.value
2 from v$statname a,v$mystat b
3 where a.statistic#=b.statistic# and a.name='redo size';
NAME VALUE
---------------------------------------------------------------- ----------
redo size 1635132SQL> insert /*+ append*/ into testredo select * from dba_tables;
???2729??
SQL> commit;
?????
SQL> select a.name,b.value
2 from v$statname a,v$mystat b
3 where a.statistic#=b.statistic# and a.name='redo size';
NAME VALUE
---------------------------------------------------------------- ----------
redo size 2419224SQL> insert /*+ append nologging */ into testredo select * from dba_tables;
commit;
???2729??
SQL>
?????
SQL> select a.name,b.value
2 from v$statname a,v$mystat b
3 where a.statistic#=b.statistic# and a.name='redo size';
NAME VALUE
---------------------------------------------------------------- ----------
redo size 3201732SQL> insert /*+ nologging */ into testredo select * from dba_tables;
???2729??
SQL> commit;
?????
SQL> select a.name,b.value
2 from v$statname a,v$mystat b
3 where a.statistic#=b.statistic# and a.name='redo size';
NAME VALUE
---------------------------------------------------------------- ----------
redo size 3954548
select (874812-0) "create" ,(1635132-874812) "insert",(2419224-1635132) "append", (3201732-2419224) "append nologging",(3954548-3201732) "nologging",(6241544-5491240) "nologging append" from dual;
6241544
create insert append append nologging nologging
---------- ---------- ---------- ---------------- ----------
874812 760320 784092 782508 752816结论：nologging 最小，再次测试结果差不多。3、索引的nologging的作用
非归档模式下
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select a.name,b.value from v$statname a,v$mystat b where a.statistic#=b.statistic# and a.name='redo size';
NAME VALUE
---------------------------------------------------------------- ----------
redo size 0
SQL> set timing on;
SQL> create table testredo as select * from tt;
?????
????: 00: 00: 05.89
SQL> select a.name,b.value from v$statname a,v$mystat b where a.statistic#=b.statistic# and a.name='redo size';
NAME VALUE
---------------------------------------------------------------- ----------
redo size 349940
????: 00: 00: 00.01
SQL> create index inx_t1 on testredo (OBJECT_ID);
??????
????: 00: 00: 07.46
SQL> select a.name,b.value from v$statname a,v$mystat b where a.statistic#=b.statistic# and a.name='redo size';
NAME VALUE
---------------------------------------------------------------- ----------
redo size 625448
????: 00: 00: 00.00
SQL> drop index inx_t1;
??????
????: 00: 00: 00.07
SQL> select a.name,b.value from v$statname a,v$mystat b where a.statistic#=b.statistic# and a.name='redo size';
NAME VALUE
---------------------------------------------------------------- ----------
redo size 656600
????: 00: 00: 00.01
SQL> create index inx_t1 on testredo (OBJECT_ID) nologging;
??????
????: 00: 00: 06.94
SQL> select a.name,b.value from v$statname a,v$mystat b where a.statistic#=b.statistic# and a.name='redo size';
NAME VALUE
---------------------------------------------------------------- ----------
redo size 930388
????: 00: 00: 00.00
SQL> select (930388-656600) nologging ,(625448-349940) logging from dual;
NOLOGGING LOGGING
---------- ----------
273788 275508创建索引用nologging 只有很小的改善（实例是用460万的数据进行的实验）。
三、表空间nologging
表空间nologging
SQL> alter tablespace users logging;
Tablespace altered.
SQL> create table testredo tablespace users as select * from dba_tables where 1>3;
SQL> select a.name,b.value from v$statname a,v$mystat b where a.statistic#=b.statistic# and a.name='redo size';
NAME VALUE
---------- ----------
redo size 99060
SQL> insert into testredo select * from dba_tables;
2740 rows created.
SQL> commit;
Commit complete.
SQL> select a.name,b.value from v$statname a,v$mystat b where a.statistic#=b.statistic# and a.name='redo size';
NAME VALUE
---------- ----------
redo size 886316
SQL> alter tablespace users nologging;
Tablespace altered.
SQL> insert into testredo select * from dba_tables;
2740 rows created.
SQL> commit;
Commit complete.
SQL> select a.name,b.value from v$statname a,v$mystat b where a.statistic#=b.statistic# and a.name='redo size';
NAME VALUE
---------- ----------
redo size 1699944
SQL> select (886316-99060),(1699944-912444) from dual;
(886316-99060) (1699944-912444)
-------------- ----------------
787256 787500
SQL> select log_mode from v$database;
LOG_MODE
------------------------------------
NOARCHIVELOG结论，没有作用。