Implications of the logging and nologging modes
You can specify nologging mode for direct-load INSERT by setting the NOLOGGING attributes for the affected table, partition, tablespace, or index by using the
The code below contains an example of nologging mode for direct-load INSERT.
ALTER TABLE emp NOLOGGING;
ALTER TABLESPACE user_data NOLOGGING;
ALTER TABLE emp MODIFY PARTITION emp_history NOLOGGING;
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:
The nologging clause IS NOT A SQL HINT, and the NOLOGGING clause is quite convoluted and dependent on several factors.
- 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".
You can use nologging for batch inserts into tables and for creating indexes:
- 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.
alter table customer nologging;
- You can insert into tables with nologging - If you use the APPEND hint and place the table in nologging mode, redo will be bypassed.
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 partition
create table...as select
direct load with SQL*Loader
direct load INSERT (using APPEND)
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:
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.