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
[email protected]>select log_mode from v$database;
LOG_MODE
------------
ARCHIVELOG
[email protected]>alter table big_table nologging;
Table altered.
[email protected]>set autotrace on statistics;
[email protected]>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
[email protected]>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
[email protected]>alter TABLE big_table logging;
Table altered.
[email protected]>select log_mode FROM v$database;
LOG_MODE
------------
NOARCHIVELOG
[email protected]>set autotrace on statistics
[email protected]>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
[email protected]>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