The nologging option is a great way to speed-up inserts and index creation.  It bypasses the writing of the redo log, significantly improving performance.   However, this approach is quite dangerous if you need to roll-forward through this time period during a database recovery. In nologging mode you are running without a safety net when you run nologging operations and you must:

• Backup before and after - You must take a backup, both before and after all nologging operations

• Only nologging operations during the nologging window -  Between the backups (the nologging processing window), ONLY nologging operations should be run in the middle of this "backup sandwich".

The nologging clause IS NOT A SQL HINT, and the NOLOGGING clause is quite convoluted and dependent on several factors.

• Database noarchivelog mode - If your database is in "noarchivelog" mode and you are no using the APPEND hint for inserts, you WILL STILL generate redo logs!
  
  
• Database archivelog mode - If you are in archivelog mode, the table must be altered to nologging mode AND the SQL must be using the APPEND hint.  Else, redo WILL be generated.

You can use nologging for batch  inserts into tables and for creating indexes:

• You can insert into tables with nologging - If you use the APPEND hint and place the table in nologging mode, redo will be bypassed. 

alter table customer nologging;

insert /*+ append */ into customer values ('hello',';there');

• You can create indexes with nologging - The only danger with using nologging is that you must re-run the create index syntax if you perform a roll-forward database recovery.  Using nologging with create index can speed index creation by up to 30%.

create index newidx . . . nologging;

• Other nologging options - Only the following operations can make use of the NOLOGGING option:
   
      alter table...move partition
      alter table...split partition
      alter index...split partition
      alter index...rebuild
      alter index...rebuild partition
      create table...as select
      create index
      direct load with SQL*Loader
      direct load INSERT (using APPEND)

For more information on using nologging for optimal performance, see my book Oracle Tuning: The Definitive Reference.

NOLOGGING clause warning!

Be very careful using UNRECOVERABLE clause and the NOLOGGING clause when performing CREATE INDEX or CREATE TABLE AS SELECT (CTAS) commands.

The CTAS with NOLOGGING or UNRECOVERABLE will send the actual create statement to the redo logs (this information is needed in the data dictionary), but all rows loaded into the table during the operation are NOT sent to the redo logs.

With NOLOGGING , although you can set the NOLOGGING attribute
for a table, partition, index, or tablespace, NOLOGGING mode does not apply to every operation performed on the schema object for which you set the NOLOGGING attribute.

It is not possible to roll forward through a point in time when an NOLOGGING operation has taken place. This can be a CREATE INDEX NOLOGGING, CREATE TABLE AS SELECT NOLOGGING, or an NOLOGGING table load.

The NOLOGGING clause is a wonderful tool since it often halves run times, but you need to remember the danger. For example, a common practice is to reorganize very large tables is to use CTAS:

Create table
   new_customer
tablespace
   new_ts
NOLOGGING
as
   select * from customer;

Drop table customer;
Rename new_customer to customer;

However, you must be aware that a roll-forward through this operation is not possible, since there are no images in the archived redo logs for this operation. Hence, you MUST take a full backup after performing any NOLOGGING operation.

---