Oracle Database Administration: The Essential Refe

Unrecoverable operations

Beginning with Oracle 7.2, it has been possible to create a table or index without writing redo log records. This option provides better performance, since significantly less I/O is required. To take advantage of this feature, specify either UNRECOVERABLE (Oracle7 syntax) or NOLOGGING (Oracle8 syntax) in the object creation statement. For example, suppose that you are moving data from another database using a database link and that you use the statement:

INSERT INTO newtable
SELECT * from oldtable@oldlink;

This method would certainly work, but redo log records would be created for each insert, which could be costly. The same task could be accomplished with the following statement:

CREATE TABLE newtable AS
SELECT * from oldtable@oldlink
NOLOGGING;

The NOLOGGING option is particularly useful when rebuilding indexes. The inclusion of the NOLOGGING keyword can cut substantial time from index creation. The SQL statement would look similar to this:

CREATE INDEX indexname ON table(column)
NOLOGGING;

Note, however, that if you experience a system failure at some point after an unrecoverable statement has completed, you will be unable to recover the transactions using the roll forward mechanism. You must recognize that a system failure has occurred and rerun the statement.