NOLOGGING operations

At the tablespace level, the LOGGING clause specifies the default LOGGING attribute for all tables, indexes, and partitions created in the tablespace and also for all objects subsequently created on it. When the tablespace LOGGING attribute is changed by the ALTER TABLESPACE statement, then all objects created after the ALTER statement will have the new LOGGING attribute; but be aware that all previously existing objects in the tablespace will not change their LOGGING attributes. The tablespace level LOGGING attribute can be overridden by the specifications at the table, index, or partition level.

A table, index, or partition can be created with the NOLOGGING option, or it can be altered later using the ALTER NOLOGGING command. It is important to note that just because an object was created with the NOLOGGING option, that does not mean no redo for that segment will ever be generated. When a segment is in the NOLOGGING mode, any direct path operation against that segment can skip redo generation, but all conventional path operations will always generate redo—regardless of the NOLOGGING attribute. The NOLOGGING option will work while running one of the following direct path operations, but not afterwards:

LOGGING is stopped only while one of the previous commands is running. For example, imagine that a user runs the following commands:

SQL> ALTER INDEX new_index NOLOGGING;
SQL> ALTER INDEX new_index REBUILD;

The actual rebuild of the index does not generate redo (only all data dictionary changes associated with the rebuild will do). Lack of clarity, any DML operation on the index will generate redo, including a direct load insert on the table to which the index belongs. Here is another example to make this point more clearer:

SQL> CREATE TABLE table_nolog_test (a number) NOLOGGING;

First let's check if the table we just created is really in NOLOGGING mode:

SQL> SELECT table_name, logging 
 2 FROM user_tables 
 3 WHERE table_name='TABLE_NOLOG_TEST';

TABLE_NAME LOGGING
---------------- -------
TABLE_NOLOG_TEST NO

1 row selected.

Now that you know that the table was created with NOLOGGING, you need to understand that all the following statements will always generate redo despite the fact that the table is in the NOLOGGING mode. For example:

SQL> INSERT INTO table_nolog_test values (1); 
SQL> UPDATE table_nolog_test SET a = 2 WHERE a = 1; 
SQL> DELETE FROM table_nolog_test WHERE a = 2;

However, the following statements will not generate redo (except from dictionary changes and indexes associated with the table):

Now that you know which commands will work with the NOLOGGING option, let's create a scenario to show the difference in redo generation when using different statements.

First we will need to build our test environment as follows:

SQL> CREATE TABLESPACE example 
DATAFILE '/u01/app/oracle/oradata/cdb1/pdb1/EXAMPLE01.DBF' SIZE 200M 
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO;

Tablespace created.

SQL> CREATE USER test IDENTIFIED BY test12c 
 2 DEFAULT TABLESPACE example
 3 TEMPORARY TABLESPACE temp;

User created.

SQL> ALTER USER test QUOTA UNLIMITED ON example;

User altered.

SQL> GRANT RESOURCE, CONNECT, SELECT ANY DICTIONARY TO test;

Grant succeeded.

SQL> CONNECT test/test12c

Connected.

SQL> CREATE TABLE test1 AS SELECT * 
 2 FROM dba_objects 
 3 WHERE rownum=0;

Table created.

Remember that our database is currently operating in the NOARCHIVELOG mode. As per the table in the LOGGING versus NOLOGGING section, a normal INSERT command should generate a normal amount of redo, and an INSERT /*+ APPEND */ command should not generate redo (only data dictionary changes). Let's see if that is really true. The following are two inserts:

SQL> SET AUTOTRACE ON STATISTICS

SQL> INSERT INTO test1 SELECT * FROM dba_objects;

88012 rows created.

Statistics
---------------------------------------------------------
 11560972 redo size
 88012 rows processed

SQL> INSERT /*+ APPEND */ INTO test1 SELECT * 
 2 FROM dba_objects;

88012 rows created.

Statistics
---------------------------------------------------------
 36772 redo size
 88012 rows processed

Yes, redo generation is behaving as expected. You can see in the example that the amount of redo generated via the simple insert was 11 MB while a direct insert generates only 36 KB.

To activate the NOLOGGING mode when using an ALTER command, you will need to add the NOLOGGING clause at the end of the ALTER command. For example:

SQL> ALTER TABLE test1 
 2 MOVE PARTITION parti_001 TABLESPACE new_ts_001 NOLOGGING;

The same applies for a CREATE INDEX command and the CREATE TABLE command. An exception is that if your CREATE TABLE command has the clause AS SELECT and you use NOLOGGING at the end of the command, then the operation will not use the NOLOGGING mode and instead will generate an alias called NOLOGGING. For example:

SQL> CREATE TABLE table_nolog_test2 NOLOGGING AS SELECT * 
 2 FROM dba_objects;

Table created.

SQL> CREATE TABLE table_nolog_test3 
 2 AS SELECT * 
 3 FROM dba_objects NOLOGGING;

Table created.

SQL> SELECT table_name, logging 
 2 FROM user_tables;


TABLE_NAME LOGGING
------------------ -------
TABLE_NOLOG_TEST NO
TABLE_NOLOG_TEST2 NO
TABLE_NOLOG_TEST3 YES

3 rows selected.

Indexed organized tables – an exception

One exception to my previous examples is when trying to use NOLOGGING with an index-organized table (IOT). It is not possible to place an IOT in the NOLOGGING mode, but we can place an index associated with such a table in the NOLOGGING mode when doing a CTAS (CREATE TABLE AS SELECT) operation. Doing so will help us to reduce redo generation in the creation process of the IOT; any intent to use INSERT /*+APPEND*/ later will not give any redo reduction advantage.

Let's test to see if what I'm saying is true. We will test the amount of redo generated when inserting bulk data in a normal table in the NOLOGGING mode and then compare the same operation against an IOT table in NOLOGGING.

First, create the tables for this exercise:

SQL> CREATE TABLE iot_test
(object_name,object_type,owner, 
CONSTRAINT iot_test_pk PRIMARY KEY(object_name,object_type,owner))
ORGANIZATION INDEX
NOLOGGING
AS
SELECT DISTINCT object_name, object_type,owner
FROM dba_objects
WHERE rownum = 0
/ 

Table created.

SQL> CREATE TABLE test5 NOLOGGING
AS
SELECT object_name, object_type,owner
FROM dba_objects
WHERE rownum = 0
/ 

Table created.

Now that we have both tables created, let's generate a bulk insert of 87,887 records into each table and compare the amount of redo each transaction generates:

SQL> set autotrace on statistics
SQL> INSERT /*+ APPEND */ INTO test5 
 2 SELECT DISTINCT object_name, object_type, owner
 3 FROM dba_objects; 

87887 rows created.

Statistics
---------------------------------------------------------
 51460 redo size
 87887 rows processed

SQL> INSERT /*+ APPEND */ INTO iot_test 
 2 SELECT DISTINCT object_name, object_type, owner
 3 FROM dba_objects; 

87887 rows created.

Statistics
---------------------------------------------------------
 16391704 redo size
 87887 rows processed

The amount of redo generated by the INSERT /*+APPEND*/ command in the normal table with NOLOGGING is 51,460 bytes and the amount generated when doing the same INSERT /*+APPEND*/ in the IOT table with NOLOGGING is 16,391,704 bytes. Clearly, the first statement used the NOLOGGING option, but the second one did not.

Now let's take a deeper look and see why the different behaviors happened. Take a look at user_tables and user_indexes to see if both tables and the IOT index are in the NOLOGGING mode:

SQL> SELECT table_name, logging FROM user_tables;

TABLE_NAME LOGGING
----------------------------------- -------
TEST5 NO
IOT_TEST

2 rows selected.

SQL> SELECT index_name, logging FROM user_indexes;

INDEX_NAME LOGGING
----------------------------------- -------
IOT_TEST_PK NO

As a result of the queries discussed, we can easily see that the normal table (TEST5) is set to NO (indicating that no logging should occur), but the IOT table (IOT_TEST) value for LOGGING is null. We can also see that the index associated with the IOT table is in the NOLOGGING mode. Now we will compare the amount of redo that will be generated when using the bulk insert in the CTAS statement.

We will use the CTAS statement to create the table TEST5 in the NOLOGGING mode and load all data at the same time. After this, we will check the DB amount of redo again after the table was created:

SQL> connect /

Connected.

SQL> CREATE TABLE test5 NOLOGGING
 2 AS
 3 SELECT DISTINCT object_name, object_type,owner
 4 FROM dba_objects
/ 

Table created.

SQL> SELECT a.name, b.value
 2 FROM v$statname a, v$mystat b
 3 WHERE a.statistic# = b.statistic#
 4 AND a.name = 'redo size';
NAME VALUE
------------------------------ ----------
redo size 119700

We can see that the amount of redo generated by the CTAS was 119,700 bytes. Now we will do the same, but using the IOT:

SQL> connect /

Connected. 

SQL> CREATE TABLE iot_test
(object_name,object_type,owner, 
CONSTRAINT iot_test_pk PRIMARY KEY(object_name,object_type,owner))
ORGANIZATION INDEX
NOLOGGING
AS
SELECT DISTINCT object_name, object_type,owner
FROM dba_objects
/ 

Table created.

SQL> SELECT a.name, b.value
 2 FROM v$statname a, v$mystat b
 3 WHERE a.statistic# = b.statistic#
 4 AND a.name = 'redo size';

NAME VALUE
------------------------------ ----------
redo size 136828

For this second example, the amount of redo generated was 136,828 bytes. This shows us that when using CTAS and NOLOGGING, it will be more efficient and generate less redo using NOLOGGING than a normal table. However, the downside is that we will not be able to make use of the NOLOGGING operation after the CTAS is done.