8. SQL Statements : SET : [No]Logging
 
Share this page                  
[No]Logging
The SET NOLOGGING statement allows a session to bypass the logging and recovery system. This may speed up certain types of update operations but must be used with extreme caution.
The SET NOLOGGING statement is intended to be used for operations for which the reduction of logging overhead and log file space usage outweigh the benefits of having to recover from transaction abort. Do not use the SET NOLOGGING statement to try to improve performance during everyday use of a production database.
Caution! If journaling is set for the database within a session where NOLOGGING was set, none of the changes will be journaled.
When transaction logging is disabled, any error that occurs when the database is being updated (including interrupts, deadlock, lock timeout, and forced abort) or any attempt to rollback a transaction causes the DBMS Server to mark the database inconsistent.
To use the SET NOLOGGING option it is recommended that the DBA:
Obtain exclusive access on the database to ensure that no other sessions are active against the tables being updated.
Be prepared to recover the database. There are two cases:
For existing databases: Checkpoint the database prior to executing the SET NOLOGGING statement. If an error occurs, the database can be restored from the checkpoint.
Loading a new database: If an error occurs, destroy the inconsistent database, create a new database, and then restart the load operation.
To enable transaction logging, issue the SET LOGGING statement
This SET statement can be issued only by the DBA of the database on which the session is operating and cannot be issued within a multi-statement transaction.
If SET NOLOGGING was in effect on a journaled database, we recommend that a checkpoint be taken immediately after the SET LOGGING statement is issued. When a session in SET NOLOGGING mode disconnects from a database, the DBMS Server executes a SET LOGGING operation to bring the database to a guaranteed consistent state before completing the disconnect.
Session disconnect is an asynchronous operation. The user should issue an explicit SET LOGGING statement if subsequent operations rely on logging being enabled before they are executed.
SET NOLOGGING is rejected if the database is set to always be logged.
Note:  SET NOLOGGING is compatible with read-only MVCC queries.