Oracle Database - How to bypass the redo log generation ?
Table of Contents
1 - 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.
2 - Articles Related
3 - Example
3.1 - 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
3.2 - 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
Advertising