参考了eygle和网上的文章
http://www.eygle.com/faq/Nologging&append.htm
http://database.ctocio.com.cn/tips/282/8200782.shtml
1 Ovewview
1) 如果对象所在的表空间或Database在Force Logging Mode中，对象的Nologging属性会被忽略掉。
2) In Nologging mode, data is modified with minimal logging(Some minimal logging is done to mark new extents invalid,
and data dictionary changes are always logged).
(Nologging并不是说一点日志都不记录,DML 肯定记，DML产生UNDO，UNDO产生REDO)
2 Only the following operations support the NOLOGGING mode.
DML:
1) Direct-path INSERT(serial or parallel) /*+append*/
2) Direct Loader(SQL*Loader)
DDL:
CREATE TABLE ... AS SELECT
CREATE TABLE ... LOB_storage_clause ... LOB_parameters ... NOCACHE |CACHE READS
ALTER TABLE ... LOB_storage_clause ... LOB_parameters ... NOCACHE |CACHE READS (to specify logging of newly created LOB columns)
ALTER TABLE ... modify_LOB_storage_clause ... modify_LOB_parameters ... NOCACHE | CACHE READS (to change logging of existing LOB columns)
ALTER TABLE ... MOVE
ALTER TABLE ... (all partition operations that involve data movement)
– ALTER TABLE ... ADD PARTITION (hash partition only)
– ALTER TABLE ... MERGE PARTITIONS
– ALTER TABLE ... SPLIT PARTITION
– ALTER TABLE ... MOVE PARTITION
– ALTER TABLE ... MODIFY PARTITION ... ADD SUBPARTITION
– ALTER TABLE ... MODIFY PARTITION ... COALESCE SUBPARTITION
CREATE INDEX
ALTER INDEX ... REBUILD
ALTER INDEX ...
3 测试
create view redo_size as
select a.name, b.value
from v$statname a, v$mystat b
where a.statistic# = b.statistic#
and lower(a.name) = 'redo size';
在非归档模式下：
SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination /opt/oracle/product/9ir2/dbs/arch
Oldest online log sequence 1328
Current log sequence 1330
SQL> create table test as select * from dba_objects where 1=0;
Table created.
SQL> select * from redo_size;
NAME VALUE
---------------------------------------------------------------- ----------
redo size 110036
SQL> insert into test select * from dba_objects;
31424 rows created.
SQL> select * from redo_size;
NAME VALUE
---------------------------------------------------------------- ----------
redo size 3637292
SQL> insert /*+ append */ into test select * from dba_objects;
31424 rows created.
SQL> select * from redo_size;
NAME VALUE
---------------------------------------------------------------- ----------
redo size 3647424
SQL> select (3647424-3637292) redo_append, (3637292-110036) redo from dual;
REDO_APPEND REDO
----------- ----------
10132 3527256
结论：在非归档模式下,hint /*+ append */只产生少量的redo.
在归档模式下
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/oracle/archive/
Oldest online log sequence 1328
Next log sequence to archive 1330
Current log sequence 1330
SQL> @redo_size
View created.
SQL> create table test as select * from dba_objects where 1=0;
Table created.
SQL> select * from redo_size;
NAME VALUE
---------------------------------------------------------------- ----------
redo size 173524
SQL> insert into test select * from dba_objects;
31425 rows created.
SQL> select * from redo_size;
NAME VALUE
---------------------------------------------------------------- ----------
redo size 3691796
SQL> insert /*+ append */ into test select * from dba_objects;
31425 rows created.
SQL> select * from redo_size;
NAME VALUE
---------------------------------------------------------------- ----------
redo size 7250224
SQL> select (7250224-3691796) append_redo, (3691796-173524) redo from dual;
APPEND_REDO REDO
----------- ----------
3558428 3518272
结论：我们看到在归档模式下,对于常规表的insert append产生和insert同样的redo
此时的insert append实际上并不会有性能提高.
但是此时的append是生效了的
SQL> select operation, count(*) from v$logmnr_contents where seg_name='TEST' group by operation;
OPERATION COUNT(*)
-------------------------------- ----------
DDL 6
DIRECT INSERT 31425
INSERT 31425
我们注意到: INSERT 和 DIRECT INSERT 都是31425条记录,也就是每条记录都记录了redo.
4.对于Nologging的table的处理
a 在归档模式下:
SQL> create table test nologging as select * from dba_objects where 1=0;
Table created.
SQL> select * from redo_size;
NAME VALUE
---------------------------------------------------------------- ----------
redo size 200276
SQL> insert into test select * from dba_objects;
31425 rows created.
SQL> select * from redo_size;
NAME VALUE
---------------------------------------------------------------- ----------
redo size 3718448
SQL> insert /*+ append */ into test select * from dba_objects;
31425 rows created.
SQL> select * from redo_size;
NAME VALUE
---------------------------------------------------------------- ----------
redo size 3724588
SQL> select (3724588-3718448) append_redo, (3718448-200276) redo from dual;
APPEND_REDO REDO
----------- ----------
6140 3518172
注意到/*+append */可以减少redo.
在非归档模式下也同样可以减少redo.
Oracle数据库中NOLOGGING和FORCE LOGGING的理解
其实nologging与表模式，插入模式，数据库运行模式(archived/unarchived)都有很大的关系：
总结如下：
注意append是一种hint;
the +append hint
Additionally, a direct load with SQL*Loader and a direct load insert can also make use of nologging.
The direct load insert is a special form. of the insert statement that uses the /*+ append */ hint.
一般我们可以这样来使用
insert /*+append+/ into mytable values(1,'alan');
数据库在归档模式下
当表模式为logging状态时，无论是append模式还是no append模式，redo都会生成。
当表模式为nologging状态时，只有append模式，不会生成redo.
数据库在非归档模式下
无论是在logging还是nologing的模式下，append的模式都不会生成redo,而no append模式下都会生成redo。
如果我想看一张表是否是logging状态,可以这样
select table_name,logging from dba_tables where table_name='tablename';
那么在Oracle内部还存在一个内部参数：_disable_logging 默认是false
通过更改为true可以让Oracle在修改表中的记录的时候完全不记录redo,这个参数要甚用。平时，我们只作为性能测试用。
force logging(强制日志)模式：
通过命令：
alter database force logging来使得Oracle无论什么操作都进行redo的写入。
通过select force_logging from v$database可以看到当前数据库强制日志模式的状态。
Note: Even though direct path load reduces the generation of redo, it is not totally eliminated.
That's because those inserts still generate undo which in turn generates redo.
That means that ordinary inserts, updates and deletes always generate redo, no matter if the underlying table or index is specifed with nologging or not.
If there is an index on the table, and an +append insert is made on the table, the indexes will produce redo.
This can be circumvented by setting the index to unusable and altering the session's skip_unusable_indexes to true. Nologging can be overriden at tablespace level using alter tablespace ... force logging. Nologging has no effect if the database is in force logging mode (which can be controlled with alter database force [no] logging mode).