Oracle nologging tips

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, 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 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 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.

---