Before Using the Set Nologging Statement
To use the SET NOLOGGING option, you as the DBA must:
• Obtain exclusive access on the database to ensure that no updates (other than those performed by the batch update operation) can occur during the operation.
• Prepare to recover the database before suspending logging. There are two cases:
– For existing databases, checkpoint the database prior to executing the operations that use the SET NOLOGGING statement. If an error halts processing, the database can be restored from the checkpoint and the process restarted.
– If loading a new database, no checkpoint is required. You can handle consistency errors by destroying the inconsistent database, creating a new database, and restarting the load operation.
Important! Do not use the SET NOLOGGING statement in an attempt to improve performance during everyday use of a production database. Because the recovery procedures for failed nologging transactions are non-automated and require full database restoration, you must consider other methods if database load performance needs improving. For assistance, see the chapter “Improving Database and Query Performance.”