Test: DML with NOLOGGING

NOLOGGING: Oracle will generate a minimal number of redo log entries in order to protect the data dictionary, and the operation will probably run faster. Logging can be disabled at the table level or the tablespace level.

If it is done at the tablespace level then we create indexes or tables in this tablespace; they will be in NOLOGGING mode.

A table or an index can be created with NOLOGGING mode or it can be altered using ALTER TABLE/INDEX NOLOGGING.

NOLOGGING is active in the following situations and while running one of the following commands but not after that.

- DIRECT LOAD (SQL*Loader)

- DIRECT LOAD INSERT (using APPEND hint)

- CREATE TABLE ... AS SELECT

- CREATE INDEX

- ALTER TABLE MOVE

- ALTER TABLE ... MOVE PARTITION

- ALTER TABLE ... SPLIT PARTITION

- ALTER TABLE ... ADD PARTITION (if HASH partition)

- ALTER TABLE ... MERGE PARTITION

- ALTER TABLE ... MODIFY PARTITION, ADD SUBPARTITON, COALESCE SUBPARTITON, REBUILD UNUSABLE INDEXES

- ALTER INDEX ... SPLIT PARTITION

- ALTER INDEX ... REBUILD

- ALTER INDEX ... REBUILD PARTITION

Logging is stopped only while one of the commands above is running.

So if a user runs this:

    ALTER INDEX new_index NOLOGGING.

The actual rebuild of the index does not generate redo (all data dictionary changes associated with the rebuild will do) but after that any DML on the index will generate redo this includes direct load insert on the table which the index belongs to.

All the following statements will generate redo despite the fact the table is in NOLOGGING mode:

- INSERT INTO new_table_nolog_test ...,

- UPDATE new_table_nolog_test SET ...,

- DELETE FROM new_table_nolog_test ..

The following will not generate redo (except from dictionary changes and indexes):

- INSERT /*+APPEND+/ ...

- ALTER TABLE new_table_nolog_test MOVE ...

- ALTER TABLE new_table_nolog_test MOVE PARTITION ...

DML on table nologging, that will not generate redo, Really?

Example: NOLOGGING (11G Archive Mode) with DML

SQL> archive log list

Database log mode               Archive Mode

Automatic archival               Enabled

Script:

--begin.sql or new.sql

column OLD_VALUE new_value OLD_VALUE

select value OLD_VALUE

from v$mystat, v$statname

where v$mystat.statistic# = v$statname.statistic#

and v$statname.name = 'redo size';

--diff.sql

select (value - &OLD_VALUE) OLD_VALUE

from v$mystat, v$statname

where v$mystat.statistic# = v$statname.statistic#

and v$statname.name = 'redo size';

- Begin Testing -

- Create TABLE

SQL> @begin

 OLD_VALUE

----------

         0

SQL> create table T_NOLOG nologging as select * from all_objects;

Table created.

SQL> @diff

old   1: select (value - &OLD_VALUE) OLD_VALUE

new   1: select (value -          0) OLD_VALUE

 OLD_VALUE

----------

    133120

REDO SIZE=133120

SQL> @begin

 OLD_VALUE

----------

    133120

SQL> create table T_LOG logging as select * from all_objects;

Table created.

SQL> @diff

old   1: select (value - &OLD_VALUE) OLD_VALUE

new   1: select (value -     133120) OLD_VALUE

 OLD_VALUE

----------

   8748596

REDO SIZE=8748596

*** create table with nologging... not generate redo, just generate for data dictionary ***

- DML -

- DELETE

SQL> @begin

 OLD_VALUE

----------

   8881716

SQL> DELETE FROM T_NOLOG;

70999 rows deleted.

SQL> @diff

old   1: select (value - &OLD_VALUE) OLD_VALUE

new   1: select (value -    8881716) OLD_VALUE

 OLD_VALUE

----------

  27076168

REDO SIZE=27076168

SQL> @begin

 OLD_VALUE

----------

  35958052

SQL> DELETE FROM T_LOG;

71000 rows deleted.

SQL> @diff

old   1: select (value - &OLD_VALUE) OLD_VALUE

new   1: select (value -   35958052) OLD_VALUE

 OLD_VALUE

----------

  27076692

REDO SIZE=27076692

- INSERT

SQL> @begin  

 OLD_VALUE

----------

  63034912

SQL> INSERT INTO T_NOLOG SELECT * FROM ALL_OBJECTS;

71000 rows created.

SQL>

SQL> @diff

old   1: select (value - &OLD_VALUE) OLD_VALUE

new   1: select (value -   63034912) OLD_VALUE

 OLD_VALUE

----------

   8493412

REDO SIZE=8493412

SQL> @begin

 OLD_VALUE

----------

  71528324

SQL>INSERT INTO T_LOG SELECT * FROM ALL_OBJECTS;

71000 rows created.

SQL> @diff

old   1: select (value - &OLD_VALUE) OLD_VALUE

new   1: select (value -   71528324) OLD_VALUE

 OLD_VALUE

----------

   8493360

REDO SIZE=8493360

- UPDATE

SQL> @begin

 OLD_VALUE

----------

  80021684

SQL> UPDATE T_NOLOG  SET OBJECT_ID=1;

71000 rows updated.

SQL> @diff

old   1: select (value - &OLD_VALUE) OLD_VALUE

new   1: select (value -   80021684) OLD_VALUE

 OLD_VALUE

----------

  24671048

REDO SIZE=24671048

SQL> @begin

 OLD_VALUE

----------

 104692732

SQL> UPDATE T_LOG SET OBJECT_ID=1;

71000 rows updated.

SQL> @diff

old   1: select (value - &OLD_VALUE) OLD_VALUE

new   1: select (value -  104692732) OLD_VALUE

 OLD_VALUE

----------

  20911424

REDO SIZE=20911424

*** On DML insert/update/delete redo size with nologging not difference... with logging. ***

- Show case "APPEND" hints

*** table NOLOGGING and not use APPEND hints ***

SQL> @begin

 OLD_VALUE

----------

 125604156

SQL> INSERT INTO T_NOLOG SELECT * FROM ALL_OBJECTS;

71000 rows created.

SQL> @diff

old   1: select (value - &OLD_VALUE) OLD_VALUE

new   1: select (value -  125604156) OLD_VALUE

 OLD_VALUE

----------

   8586036

REDO SIZE=8586036

SQL> @begin

 OLD_VALUE

----------

 142830588

SQL> INSERT /*+ APPEND */ INTO T_NOLOG  SELECT * FROM ALL_OBJECTS;

71000 rows created.

SQL> @diff

old   1: select (value - &OLD_VALUE) OLD_VALUE

new   1: select (value -  142830588) OLD_VALUE

 OLD_VALUE

----------

    29448

REDO SIZE=29448

*** table LOGGING, and use APPEND hints ***

SQL> @begin

 OLD_VALUE

----------

 134190192

SQL> INSERT /*+ APPEND */ INTO T_LOG SELECT * FROM ALL_OBJECTS;

71000 rows created.

SQL> @diff

old   1: select (value - &OLD_VALUE) OLD_VALUE

new   1: select (value -  134190192) OLD_VALUE

 OLD_VALUE

----------

   8640396

REDO SIZE=8640396

*** make table logging to nologging ***

SQL> alter table t_log nologging ;

Table altered.

SQL> @begin

 OLD_VALUE

----------

 142874676

SQL> INSERT /*+ APPEND */ INTO T_LOG SELECT * FROM ALL_OBJECTS;

71000 rows created.

SQL> @diff

old   1: select (value - &OLD_VALUE) OLD_VALUE

new   1: select (value -  142874676) OLD_VALUE

 OLD_VALUE

----------

     27956

REDO SIZE=27956

*** APPEND hints on table "logging" not difference (generate redo). ***

*** If  "alter table nologging"  before, and then insert (append)... it's work with nologging (not generate redo, just redo for data dictionary). ***

Example: NOLOGGING (NO Archive Mode) with DML

SQL> archive log list

Database log mode              No Archive Mode

Automatic archival             Disabled

- Create TABLE -

SQL> @begin

 OLD_VALUE

----------

         0

SQL> create table T_NOLOG nologging as select * from all_objects;

Table created.

SQL> @diff

old   1: select (value - &OLD_VALUE) OLD_VALUE

new   1: select (value -          0) OLD_VALUE

 OLD_VALUE

----------

    109020

SQL> @begin

 OLD_VALUE

----------

    109020

SQL> create table T_LOG logging as select * from all_objects;

Table created.

SQL> @diff

old   1: select (value - &OLD_VALUE) OLD_VALUE

new   1: select (value -     109020) OLD_VALUE

 OLD_VALUE

----------

    106360

*** When create table (noarchive mode) LOGGING table not different NOLOGGING table ***

- DML -

SQL> @begin

 OLD_VALUE

----------

    215380

SQL> DELETE FROM T_NOLOG;

49618 rows deleted.

SQL> @diff

old   1: select (value - &OLD_VALUE) OLD_VALUE

new   1: select (value -     215380) OLD_VALUE

 OLD_VALUE

----------

  18318540

SQL> @begin

 OLD_VALUE

----------

  18533920

SQL> DELETE FROM T_LOG;

49619 rows deleted.

SQL> @diff

old   1: select (value - &OLD_VALUE) OLD_VALUE

new   1: select (value -   18533920) OLD_VALUE

 OLD_VALUE

----------

  18319920

SQL> @begin

 OLD_VALUE

----------

  36853840

SQL> INSERT INTO T_NOLOG SELECT * FROM ALL_OBJECTS;

49619 rows created.

SQL> @diff

old   1: select (value - &OLD_VALUE) OLD_VALUE

new   1: select (value -   36853840) OLD_VALUE

 OLD_VALUE

----------

   5468288

SQL> @begin

 OLD_VALUE

----------

  42322128

SQL> INSERT INTO T_LOG SELECT * FROM ALL_OBJECTS;

49619 rows created.

SQL> @diff

old   1: select (value - &OLD_VALUE) OLD_VALUE

new   1: select (value -   42322128) OLD_VALUE

 OLD_VALUE

----------

   5467828

SQL> @begin

 OLD_VALUE

----------

  93911152

SQL> UPDATE T_NOLOG SET OBJECT_ID=1;

49619 rows updated.

SQL> SQL> @diff

old   1: select (value - &OLD_VALUE) OLD_VALUE

new   1: select (value -   93911152) OLD_VALUE

 OLD_VALUE

----------

  15555936

SQL> @begin

 OLD_VALUE

----------

 109467088

SQL> UPDATE T_LOG SET OBJECT_ID=1;

49619 rows updated.

SQL> @diff

old   1: select (value - &OLD_VALUE) OLD_VALUE

new   1: select (value -  109467088) OLD_VALUE

 OLD_VALUE

----------

  16326484

*** On DML INSERT/UPDATE/DELETE not different between NOLOGGING and LOGGING ***

- INSERT /*+ APPEND */ -

SQL> @begin

 OLD_VALUE

----------

 125793740

SQL>  INSERT INTO T_NOLOG SELECT * FROM ALL_OBJECTS;

49619 rows created.

SQL> @diff

old   1: select (value - &OLD_VALUE) OLD_VALUE

new   1: select (value -  125793740) OLD_VALUE

 OLD_VALUE

----------

   5823672

SQL> @begin

 OLD_VALUE

----------

 131617412

SQL> INSERT /*+ APPEND */ INTO T_NOLOG SELECT * FROM ALL_OBJECTS;

49619 rows created.

SQL> @diff

old   1: select (value - &OLD_VALUE) OLD_VALUE

new   1: select (value -  131617412) OLD_VALUE

 OLD_VALUE

----------

     28484

SQL> @begin

 OLD_VALUE

----------

 131709848

SQL> INSERT INTO T_LOG SELECT * FROM ALL_OBJECTS;

49619 rows created.

SQL> @diff

old   1: select (value - &OLD_VALUE) OLD_VALUE

new   1: select (value -  131709848) OLD_VALUE

 OLD_VALUE

----------

   5823620

SQL> @begin

 OLD_VALUE

----------

 131645896

SQL>  INSERT /*+ APPEND */ INTO T_LOG SELECT * FROM ALL_OBJECTS;

49619 rows created.

SQL> @diff

old   1: select (value - &OLD_VALUE) OLD_VALUE

new   1: select (value -  131645896) OLD_VALUE

 OLD_VALUE

----------

     26816

*** On NoArchive Mode, Don't mind tables be nologging/logging...  just use /*+ APPEND */ ,that will not generate redo (just data dictionary) ***

*** On Archive Mode, TABLEs must be nologging... and use  /*+ APPEND */, that will not generate redo (just data dictionary) ***

by Surachart Opun