From Web3us, LLC

The following is from: here

Logging Mode

Direct-load INSERT operations can be done with or without logging of redo information. You can specify no-logging mode for the table, partition, or index into which data will be inserted by using an ALTER TABLE, ALTER INDEX, or ALTER TABLESPACE statement.

• Direct-load INSERT with logging. This mode can do full redo logging for instance and media recovery. Logging is the default mode. The database must be in ARCHIVELOG mode for online redo logs to be archived to tape. Otherwise, instance crashes are recoverable, but disk failures are not recoverable.

• Direct-load INSERT with no-logging. In this mode, data is inserted without redo or undo logging. (Some minimal logging is still done for marking new extents invalid, and dictionary changes are always fully logged.) When applied during media recovery, the extent invalidation records mark a range of blocks as logically corrupt, since the redo data is not logged.

The no-logging mode improves performance because it generates much less log data. The user is responsible for backing up the data after a no-logging insert operation in order to be able to perform media recovery.

There is no interaction between no-logging mode and discrete transactions, which always generate redo information. Discrete transactions can be issued against tables that use the no-logging attribute.

Note:

Logging/no-logging mode is not a permanent attribute of the table, partition, or index. After the database object inserted into has been populated with data and backed up, you can set its status to logging mode so that subsequent changes will be logged.

Table 22-2 compares the LOGGING and NOLOGGING modes for direct-load and conventional INSERT. Table 22-2 Summary of LOGGING and NOLOGGING clauses Insert Type LOGGING NOLOGGING

Direct-load INSERT

Yes: recoverability requires ARCHIVELOG database mode

Yes: requires NOLOGGING attribute for tablespace, table, partition, or index

Conventional INSERT

Yes (default): recoverability requires ARCHIVELOG database mode

No

See Also

"Discrete Transaction Management" Examples of No-Logging Mode

You can specify no-logging mode for direct-load INSERT by setting the NOLOGGING attribute of the table into which rows are inserted, for example:

ALTER TABLE emp NOLOGGING; ALTER SESSION ENABLE PARALLEL DML; INSERT /*+ PARALLEL(emp,12) */ INTO emp SELECT /*+ PARALLEL(t_emp,12) */ * FROM t_emp; COMMIT;

You can also set the NOLOGGING attribute for a partition, tablespace, or index; for example:

ALTER TABLE emp MODIFY PARTITION emp_lmnop NOLOGGING;

ALTER TABLESPACE personnel NOLOGGING;

ALTER INDEX emp_ix NOLOGGING;

ALTER INDEX emp_ix MODIFY PARTITION eix_lmnop NOLOGGING;

SQL Statements That Can Use No-Logging Mode

Although you can set the NOLOGGING attribute for a table, partition, index, or tablespace, no-logging mode does not apply to every operation performed on the schema object for which you set the NOLOGGING attribute. Only the following operations can make use of no-logging mode:

• direct load (SQL*Loader)

• direct-load INSERT

• CREATE TABLE ... AS SELECT

• CREATE INDEX

• ALTER TABLE ... MOVE PARTITION

• ALTER TABLE ... SPLIT PARTITION

• ALTER INDEX ... SPLIT PARTITION

• ALTER INDEX ... REBUILD

• ALTER INDEX ... REBUILD PARTITION

• INSERT, UPDATE, and DELETE on LOBs in NOCACHE NOLOGGING mode stored out of line

All of these SQL statements can be parallelized. They can execute in logging or no-logging mode for both serial and parallel execution.

Other SQL statements are unaffected by the NOLOGGING attribute of the schema object. For example, the following SQL statements are unaffected by NOLOGGING mode: UPDATE and DELETE (except on some LOBs, as noted above), conventional path INSERT, and various DDL statements not listed above.