Home > SQL-PL/SQL > Which One? LOGGING or NOLOGGING?

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.

CREATE TABLE talip_test3 (

isim varchar2(10)

)

NOLOGGING;

insert a row;

insert into talip_test3 values(‘talip’);

commit;

delete a row;

delete from talip_test3;

After rollback, you can see deleted row. So, the rollback processis not associated with  LOGGING / NOLOGGING .

rollback;

select * from talip_test3;

Talip Hakan Öztürk

  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: