Implications of logging and nologging Modes

Logging and nologging are two modes you can set for database objects, such as tables, partitions, and indexes. If the database objects are set to logging mode, which is the default, all the changes made to them are fully recorded in redo logs. If you turn off logging mode for these objects and modify them, only minimal redo is generated. Tables, indexes, or partitions may be set to nologging mode for loading a large block of data with the direct-load INSERT operation. When the nologging mode is set for a direct-load, the insert statements are not logged in the redo logs, and thus, the loading process is faster.

When you are performing the direct-load INSERT in the nologging mode, data is inserted without generating redo information. Even though some minimal logging is still done, recovery is almost impossible without further back up. In contrast, data dictionary changes are always fully logged.
The nologging mode has a performance advantage because it generates much less redo information. On the other hand, because the redo logs do not contain the information about the inserted values during the period when the table was in nologging mode, the database is not recoverable from the last backup. You are responsible for backing up the data after a nologging INSERT operation in order to be able to perform successful media recovery. Thus, upon the completion of the direct-load operation on a table, you should reset the table to logging mode and immediately backup the datafiles containing the table.

A direct-load insert can be used to append data to a table from another table. This method of insertion can bypass the buffer cache and directly write the data blocks to the data file. The direct-load insert can be performed in parallel. If the table is in NOLOGGING mode, it will not generate redo entries. To enable direct-load inserts, you must specify the APPEND hint if the table is in serial mode. If the table is in parallel mode, the APPEND hint is optional. A hint is used to specify your own data retrieval path rather than using the one calculated by the Oracle optimizer. A hint is specified between /*+ and */ .
If there are triggers or referential integrity constraintson the table, Oracle ignores the APPEND hint and does a conventional insert. There is no special keyword to enable or disable direct-load inserts in parallel mode. To improve load speed, indexes can be dropped and rebuilt after the load completes. During direct-load insert, exclusive locks are obtained on the table; no DML activity can be performed. The following examples illustrate how to use direct-load insert.
When the table is in serial mode, that is, the degree of parallelism is 1, to enable the direct-load insert you must specify the APPEND hint.

ALTER TABLE MON_TRANS NOPARALLEL;
INSERT /*+ APPEND */ INTO MON_TRANS
SELECT * FROM DAILY_TRANS;

ALTER TABLE MON_TRANS NOLOGGING;

When the database is in ARCHIVELOG mode, if a media recovery is done, Oracle cannot load the records to the table from the archive log files. Therefore, you must make a backup of the table or tablespace when the direct-load insert is complete. If the database is in NOARCHIVELOG mode, having the table in NOLOGGING mode does not have any implications and can tremendously improve the performance of direct-load inserts. In serial direct-load insert, Oracle inserts data blocks above the high-water mark of the table segment. The data is visible to other users only when a commit is issued, which then moves the high-water mark to the new position.