Reduce Redo during massive data loading operation

- For massive data loading operation we just create a simple table with nologging mode, and no index on this table.
- If there is an index in a table, index would not be appended and it must be modified and this modifications must be logged by oracle. So redo/undo for the table itself is not generated and it generate for the indexes always.

Scenario #1 : Create table( NOLOGGING mode ) with no index associated.
===========
SQL>CREATE TABLE redo_table NOLOGGING AS SELECT * FROM all_objects WHERE 1=0;

Table created.

SQL>SET autotrace on statistics

SQL>INSERT /*+ append */ INTO redo_table SELECT * FROM all_objects;

68433 rows created.

Statistics
----------------------------------------------------------
       812  recursive calls
      1119  db block gets
     80651  consistent gets
         0  physical reads
     18304  redo size
       824  bytes sent via SQL*Net to client
       815  bytes received via SQL*Net from client
         3  SQL*Net roundtrips to/from client
      1459  sorts (memory)
         0  sorts (disk)
     68433  rows processed

Scenario #2 : Create table( NOLOGGING mode ) with index associated.
===========
In this worst case scenario, note the redo size is larger due to index operation.

SQL>CREATE INDEX redo_index ON redo_table(object_name);

Index created.

SQL> INSERT /*+ append */ INTO redo_table SELECT * FROM all_objects;

68434 rows created.

Statistics
----------------------------------------------------------
  1987  recursive calls
  7740  db block gets
 81498  consistent gets
     1  physical reads
10378204  redo size
   823  bytes sent via SQL*Net to client
   815  bytes received via SQL*Net from client
     3  SQL*Net roundtrips to/from client
  1455  sorts (memory)
     0  sorts (disk)
 68434  rows processed

With index present in this scenario, there is significantly more redo generated.

In massive application development index is an necessary operation for query processing and should be unavoidable. So before doing massive inserts in the table, disable index and then perform data loading operation and rebuild index.

SQL> ALTER INDEX redo_index UNUSABLE;

Index altered.

SQL> SELECT status FROM dba_indexes WHERE index_name ='REDO_INDEX';

STATUS
--------
UNUSABLE

SQL> INSERT /*+ append */ INTO redo_table SELECT * FROM all_objects;

68434 rows created.

Statistics
----------------------------------------------------------
     823  recursive calls
    1120  db block gets
   80661  consistent gets
       0  physical reads
   18320  redo size
     823  bytes sent via SQL*Net to client
     816  bytes received via SQL*Net from client
       3  SQL*Net roundtrips to/from client
    1462  sorts (memory)
       0  sorts (disk)
   68434  rows processed

SQL> ALTER INDEX redo_index REBUILD ONLINE;

Index altered.

SQL> SELECT status FROM dba_indexes WHERE index_name ='REDO_INDEX';

STATUS
--------
VALID

Note, In this fashion you will end up with a nicely compacted index from unusable to valid sate and best data loading operation of end to end.