Logged operation has to generate redo for every change data or undo block, nologging operations indicate that the database operation is not logged in the online redo log file. Even though a small invalidation redo record is still written to the online redo log file, nologging operations skip the redo generation of the corresponding inserted data. nologging can be extremely beneficial for the following reasons:
* data written to the redo is minimized dramatically
* time to insert into a large table or index or LOB can be reduced dramatically
* performance improves for parallel creation of large tables or index
-CREATE TABLE ... AS SELECT (CTAS)
- 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 non-internal LOBs in NOCACHE NOLOGGING
Expand|Select|Wrap|Line Numbers
- SQL> create table TEST.T nologging as select num_rows from dba_tables;
- Table created.
- SQL> select count(*) from test.t;
- COUNT(*)
- ----------
- 1517
- SQL> create index my_index on TEST.T(NUM_ROWS) nologging;
- Index created.
- SELECT NAME, UNRECOVERABLE_CHANGE#,
- TO_CHAR (UNRECOVERABLE_TIME,'DD-MON-YYYY HH:MI:SS')
- FROM V$DATAFILE;
- UNRECOVERABLE_CHANGE# TO_CHAR(UNRECOVERABLE_TIME,'DD'
- --------------------- -------------------------------------------
- /oracle/oracle/oradata/oracle/orcl/system01.dbf
- 451816 13-NOV-2007 02:53:21
- /oracle/oracle/oradata/oracle/orcl/undotbs10.dbf
- /oracle/oracle/oradata/oracle/orcl/trade_121.dbf
- 451733 13-NOV-2007 02:50:13
ORA-01578: ORACLE data block corrupted (file # 3, block # 514)
ORA-01110: data file 3: '/oracle/oracle/oradata/oracle/orcl/trade_121.dbf'
ORA-26040: Data block was loaded using the NOLOGGING option
However, NOLOGGING is intended for configurations in which media recovery or the recovery of the corresponding object is not important .
Thanks & Regards,
Vinod Sadanandan
Oracle DBA