Avoiding Redo Creation- Tables and Constraints

When you are creating a table, you have the option of specifying the NOLOGGING clause. The NOLOGGING feature can greatly reduce the amount of redo generation for certain types of operations. Sometimes, when you are working with large amounts of data, it is desirable, for performance reasons, to reduce the redo generation when you initially create and insert data into a table.

Notice that I did say greatly “reduce” the amount of redo and not eliminate. The downside to reducing the redo generation is that you cannot recover the data created via NOLOGGING in the event a failure occurs after the data is loaded (and before you can back up the table). If you can tolerate some risk of data loss, then use NOLOGGING, but back up the table soon after the data are loaded. If your data is critical, then do not use NOLOGGING. If your data can be easily re-created, then NOLOGGING is desirable when you are trying to improve performance of large data loads.


The NOLOGGING feature never affects redo generation for normal DML statements (INSERT, UPDATE, and DELETE) but can significantly reduce redo generation for the following types of operations:
• SQL*Loader direct-path load
• Direct-path INSERT
• CREATE TABLE AS SELECT
• ALTER TABLE MOVE
• Creating or rebuilding an index

There are some quirks (features) when using NOLOGGING. If your database is in FORCE LOGGING mode, then redo is generated for all operations, regardless of NOLOGGING. Likewise, when you are loading a table, if the table has a referential foreign key constraint defined, then redo is generated regardless of whether you specify NOLOGGING.

NOLOGGING can be specified at a statement level, table, or tablespace level. It is easier to specify the NOLOGGING at the table level.


You can determine the effects of NOLOGGING by measuring the amount of redo generated for an operation with logging enabled versus operating in NOLOGGING mode. If you have a development environment that you can test in, you can monitor how often the redo logs switch while the operation is taking place. Another simple test is the timing of the job with and without logging. NOLOGGING should be faster.

Creating a Table from a Query

It is convenient to create a table based on the definition of an existing table. For instance, say you want to create a quick backup of a table before you modify the table’s structure or data. Use the CREATE TABLE AS SELECT (CTAS) statement, for example:

SQL> create table inventory_backup as select * from inventory;

The previous statement creates an identical table, complete with data. If you do not want the data included and you just want the structure of the table replicated, then provide a WHERE clause that always evaluates to false, such as 1=2:

SQL> create table inventory_empty as select * from inventory

where 1=2;

The CTAS technique does not create any indexes, constraints, or triggers. You have to create indexes and triggers separately if you need those objects from the original table. CTAS is useful when backing up a table or troubleshooting a data problem in conjunction with flashback query, which we will discuss in a later chapter.

You may also like