Oracle Database - How to bypass the redo log generation ?

About

A direct path insert will only bypass Redo Log generation in two cases :

the database is in NOARCHIVELOG Mode
the operation is performed on a table marked as NOLOGGING

The insert /*+ APPEND */ will minimize redo log generation in all cases, as it minimize the amount of UNDO generated. The redo that would otherwise be generated for the UNDO information is not created, but ultimately the logging mode of the target table is what will dictate whether redo is generated for the table or not.

Articles Related

Example

ARCHIVELOG and NOLOGGING

gerardnico@orcl>select log_mode from v$database;

LOG_MODE
------------
ARCHIVELOG

gerardnico@orcl>alter table big_table nologging;

Table altered.

gerardnico@orcl>set autotrace on statistics;
gerardnico@orcl>insert into big_table select * from all_objects;

66651 rows created.


Statistics
----------------------------------------------------------
       6373  recursive calls
       8389  db block gets
     193231  consistent gets
          5  physical reads
    7481096  redo size
        909  bytes sent via SQL*Net to client
        952  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
       1561  sorts (memory)
          0  sorts (disk)
      66651  rows processed

gerardnico@orcl>insert /*+ APPEND */ into big_table select * from all_objects;

66651 rows created.


Statistics
----------------------------------------------------------
       5860  recursive calls
       1394  db block gets
     192176  consistent gets
          0  physical reads
      33584  redo size
        893  bytes sent via SQL*Net to client
        966  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
       1554  sorts (memory)
          0  sorts (disk)
      66651  rows processed

NOARCHIVELOG and LOGGING

gerardnico@orcl>alter TABLE big_table logging;

Table altered.

gerardnico@orcl>select log_mode FROM v$database;

LOG_MODE
------------
NOARCHIVELOG

gerardnico@orcl>set autotrace on statistics
gerardnico@orcl>insert INTO big_table SELECT * FROM all_objects;

66651 rows created.


Statistics
----------------------------------------------------------
       6279  recursive calls
       8475  db block gets
     193915  consistent gets
          6  physical reads
    7486972  redo size
        899  bytes sent via SQL*Net to client
        952  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
       1558  sorts (memory)
          0  sorts (disk)
      66651  rows processed

gerardnico@orcl>insert /*+ APPEND */ INTO big_table SELECT * FROM all_objects;

66651 rows created.


Statistics
----------------------------------------------------------
       5860  recursive calls
       1482  db block gets
     192172  consistent gets
          0  physical reads
      38736  redo size
        888  bytes sent via SQL*Net to client
        966  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
       1554  sorts (memory)
          0  sorts (disk)
      66651  rows processed