Which One? LOGGING or NOLOGGING?

LOGGING is a keyword that used on creating the index, table or tablespace. If we use LOGGING when creating the object then DML operations on the objects are logged in redo log file. If we use NOLOGGING when creating the object, in some cases DML operations on the objects are not logged in redo log file. There are a lot of questions asked on the forums about LOGGING/NOLOGGING. Some of the questions as follows: What is the advantage of NOLOGGING? When should we use NOLOGGING ? If NOLOGGING is used on the tablespace level, can I use LOGGING on the tables which are in the NOLOGGING tablespace?

OK. Let us examine one by one.

let’s create a NOLOGGING tablespace.

CREATE TABLESPACE NOLOGGING_TS DATAFILE

‘/data_TALIPDB/nologging_ts.dbf’ SIZE 1024M AUTOEXTEND OFF

NOLOGGING

ONLINE

PERMANENT

EXTENT MANAGEMENT LOCAL UNIFORM SIZE 10M

BLOCKSIZE 8K

SEGMENT SPACE MANAGEMENT AUTO

FLASHBACK ON;

Now, I will create a table in this tablespace. And I will not specify LOGGING or NOLOGGING when creating the table.

CREATE TABLE talip_test(

id NUMBER

)

TABLESPACE nologging_ts;

Check the table LOGGING status with following query. You will see LOGGING=NO

SELECT table_name, logging

FROM dba_tables

WHERE tablespace_name = ‘NOLOGGING_TS’

TABLE_NAME LOGGING

—————————— ——-

TALIP_TEST NO

Now, I will create table in NOLOGGING_TS tablespace with LOGGING clause.

CREATE TABLE talip_test2 (

id NUMBER

)

TABLESPACE nologging_ts

LOGGING;

Rerun above query to see table LOGGING status. You will see LOGGING=YES

SELECT tablespace_name, logging

FROM dba_tables

WHERE table_name = ‘TALIP_TEST2’

TABLE_NAME                  LOGGING

—————————— ——-

TALIP_TEST                         NO

TALIP_TEST2                       YES

In that case, If you specify NOLOGGING on the tablespace level, then default logging status will be NOLOGGING that the objects to be created within the tablespace.

If you create a table as NOLOGGING then you can change its logging status to LOGGING. Or the opposite might happen.

ALTER TABLE talip_test LOGGING;

ALTER TABLE talip_test2 NOLOGGING;

This situation applies in tablespace. If you create a tablespace as NOLOGGING then you can change its logging status to LOGGING.

ALTER TABLESPACE nologging_ts LOGGING;

After changing a tablespace logging status to LOGGING, NOLOGGING created objects will remain as NOLOGGING. If you create a new table in this tablespace then created table logging status will be LOGGING. In some cases, logging can continue on NOLOGGING. So what are these conditions?

Table mode	Insert mode	Archive Log Mode	Redo generation
LOGGING	APPEND	ARCHIVE LOG	REDO generated
NOLOGGING	APPEND	ARCHIVE LOG	no REDO
LOGGING	No APPEND	ARCHIVE LOG	REDO generated
NOLOGGING	No APPEND	ARCHIVE LOG	REDO generated
LOGGING	APPEND	NO ARCHIVE LOG	no REDO
NOLOGGING	APPEND	NO ARCHIVE LOG	no REDO
LOGGING	No APPEND	NO ARCHIVE LOG	REDO generated
NOLOGGING	No APPEND	NO ARCHIVE LOG	REDO generated

“Direct Path Load” operations on the NOLOGGING table are not generated redo. If “force logging” is enabled then always redo is generated. Even if table is LOGGING mode, the “Direct Path Load” operations doesn’t generate redo log in NO ARCHIVE LOG. Even if the database in ARCHIVE LOG mode, redo is not generated on NOLOGGING table.

Direct Load Insert is a faster way of running an INSERT statement. It is particularly useful for inserting large numbers of rows. Direct Load Insert differs from Conventional Insert in that it bypasses the buffer cache.

Creating a table as NOLOGGING takes less time than to create LOGGING. Let a simple test.

SQL> set timing on

SQL> create table talip_logging logging as select * from dba_tables;

Table created.

Elapsed: 00:00:01.20

Now let’s create it with nologging.

SQL> create table talip_nologging nologging as select * from dba_tables;

Table created.

Elapsed: 00:00:00.60

And there is an important point. If you delete a row from NOLOGGING table then you can rollback. Baceause of it is not depended on LOGGING/NOLOGGING. It is depended on UNDO segments. Let’s do a simple test.

Create a NOLOGGING table.