Relation between Oracle Direct Path Insert, NOLOGGING, Unusable Indexes

Sometimes lots of us confused as mistakenly thinking DIRECT PATH INSERT (using APPEND hint) will not generate or generate LESS redo.

In reality, it is true, but there are other conditions which must be true:



I - Database is in archivelog mode:

Case 1 - Table is in logging mode, conventional insert and no index:

SQL> set autotrace traceonly statistics

SQL> create table myt1 as select * from dba_objects where 1=0;

Table created.

SQL> insert into myt1 select * from dba_objects;

69398 rows created.



Statistics
----------------------------------------------------------
...
8113324  redo size
...
69398  rows processed

Case 2 - Table is in nologging mode, conventional insert and no index:

SQL> create table myt1 as select * from dba_objects where 1=0;

Table created.

SQL> alter table myt1 nologging;  

Table altered.

SQL> insert into myt1 select * from dba_objects;

69398 rows created.


Statistics
----------------------------------------------------------
...
8113220  redo size  ---> not so big difference
...
69398  rows processed

SQL>

Case 3 - Table is in logging mode, direct-path insert and no index:

SQL> create table myt1 as select * from dba_objects where 1=0;

Table created.

SQL> insert /*+ APPEND */ into myt1 select * from dba_objects;

69398 rows created.


Statistics
----------------------------------------------------------
...
8165696  redo size   ---> not so big difference
...
69398  rows processed

Case 4 - Table is in nologging mode, direct-path insert and no index:

SQL> create table myt1 as select * from dba_objects where 1=0;

Table created.

SQL> alter table myt1 nologging;

Table altered.

SQL> insert /*+ APPEND */ into myt1 select * from dba_objects;

69398 rows created.


Statistics
----------------------------------------------------------
...
37876  redo size   ---> so big difference
...
69398  rows processed

Case 5 - Table is in logging mode, direct-path insert and index with logging:

SQL> create table myt1 as select * from dba_objects where 1=0;

Table created.

SQL> create index myt1$IDX1 on myt1(object_id, object_name);

Index created.

SQL>  insert /*+ APPEND */ into myt1 select * from dba_objects;

69399 rows created.


Statistics
----------------------------------------------------------
...
20162152  redo size
...
69399  rows processed

SQL>

Case 6 - Table is in nologging mode, direct-path insert and index with logging:

SQL> alter table myt1 nologging;

SQL> insert /*+ APPEND */ into myt1 select * from dba_objects;

69399 rows created.


Statistics
----------------------------------------------------------
...
12102428  redo size   ---> not so big difference
...
69399  rows processed

Case 7 - Table is in nologging mode, direct-path insert and index with nologging:

SQL> alter index myt1$IDX1 nologging;

Index altered.

SQL> insert /*+ APPEND */ into myt1 select * from dba_objects;

69399 rows created.


Statistics
----------------------------------------------------------
...
12044464  redo size   ---> not so big difference
...
69399  rows processed

Case 8 - Table is in nologging mode, direct-path insert and index with nologging and skiping unusable indexes:

SQL> alter index myt1$IDX1 unusable;

Index altered.

SQL> alter session set skip_unusable_indexes=true;

Session altered.

SQL> insert /*+ APPEND */ into myt1 select * from dba_objects;

69399 rows created.


Statistics
----------------------------------------------------------
...
37980  redo size   ---> so big difference
...
69399  rows processed

II - Database is in noarchivelog mode:

SQL> create table myt1 as select * from dba_objects where 1=0;

Table created.

SQL> insert /*+ APPEND */ into myt1 select * from dba_objects;

69398 rows created.


Statistics
----------------------------------------------------------
...
38444  redo size
...
69398  rows processed

That is difference how direct path insert operates in different environments (Archivelog or Noarchivelog) and object properties (Nologging option of tablespace, table, index and unusable state of index):

   1. Direct path insert does not generate redo if database in NOARCHIVELOG mode, even table is in LOGGING mode.
   2. Direct path insert does not generate redo if database in ARCHIVELOG mode and table or tablespace is in NOLOGGING mode.

NOTE: if the object created with NOLOGGING option, take backup of database if you think this information will be permanent and can not be reproduced easily.