nologging

redo重做日志是Oracle数据库恢复(recovery)的基础；但在很多情况下可以通过禁用重做日志的产生来加速SQL语句的完成，也就是我们所说的可nologging化的操作，这些操作大多是或串行的或并行的数据载入。

那么哪些操作是允许被nologging化的呢？首先需要注意的是普通的DML操作，即:常规insert,update,和delete(以及merge)总是不能被nologging执行的。但以下SQL语句则可以以nologging选项执行：

direct load (SQL*Loader)
direct load INSERT (using APPEND hint)
CREATE TABLE … AS SELECT
CREATE INDEX
ALTER TABLE … MOVE PARTITION
ALTER TABLE … SPLIT PARTITION
ALTER INDEX … SPLIT PARTITION
ALTER INDEX … REBUILD
ALTER INDEX … REBUILD PARTITION
INSERT, UPDATE, and DELETE on LOBs in NOCACHE NOLOGGING mode stored out of line

以上列出的语句，其产生undo和redo重做日志几乎可以完全禁绝。因为都是数据载入语句(或者装载索引)其所产生的新的区间(new extent)在执行过程中被标记为无效的(invalid),同时仅有少量的由数据字典变更引起的重做日志会产生。

The NOLOGGING clause doesn’t prevent redo on all operations, but rather only on a subset. I searched the documentation for examples of this…

http://st-doc.us.oracle.com/11/112/server.112/e16541/parallel007.htm?term=nologging+generate+redo#VLDBG1536

[NO]LOGGING Clause

The [NO]LOGGING clause applies to tables, partitions, tablespaces, and indexes. Virtually no log is generated for certain operations (such as direct-path INSERT) if the NOLOGGING clause is used. The NOLOGGING attribute is not specified at the INSERT statement level but is
instead specified when using the ALTER or CREATE statement for a table, partition, index, or tablespace.

When a table or index has NOLOGGING set, neither parallel nor serial direct-path INSERT operations generate redo logs. Processes running with the NOLOGGING option set run faster because no redo is generated. However, after a NOLOGGING operation against a table,
partition, or index, if a media failure occurs before a backup is performed, then all tables, partitions, and indexes that have been modified might be corrupted.

Direct-path INSERT operations (except for dictionary updates) never generate redo logs if the NOLOGGING clause is used. The NOLOGGING attribute does not affect undo, only redo. To be precise, NOLOGGING allows the direct-path INSERT operation to generate a negligible
amount of redo (range-invalidation redo, as opposed to full image redo).

But I did find an Ask Tom article which is more explicit about what operations generate redo despite the NOLOGGING clause:

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:5280714813869

This has a nice table of operations with either No/Archive and No/Logging specified, and you can see redo is generated most of the time.

So to sum up the NOLOGGING clause only works with certain operations and we cannot expect all REDO to be completely halted.

Another item to consider is whether the Indexes on the tables were created with NOLOGGING or not… This is covered in Index generates high redo, although it is in NOLOGGING (Doc ID 1235234.1), so please reveiw that note to see if there are some indexes that can be recreated to reduce redo further.

Oracle中可以nologging执行的操作

Know more about Oracle Nologging