Oracle Database - Direct (path insert|load) (/*+ APPEND */)

Card Puncher Data Processing

About

A direct-path insert is also known as:

  • direct load

A direct-path insert is a bulk operation which will only bypass redo log generation in three cases :

  • the database is in NOARCHIVELOG mode database
  • the operation is performed on a table marked as NOLOGGING
  • the statement is bulk insert in the form INSERT AS SELECT

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.

As a recovery is not possible from the redo log, you need coordination with the DBA to schedule necessary backup.

Why it's faster

A conventional path load executes SQL INSERT statements to populate tables. In contrast, a direct path load eliminates much of the database overhead by formatting data blocks and writing them directly to the database files. Direct writes operate on blocks above the high water mark and write directly to disk, bypassing the database buffer cache. Direct reads read directly from disk into the PGA, again bypassing the buffer cache.

Reasons to not set NOLOGGING and /*+ APPEND */ on all insert

Few reasons not to set NOLOGGING and use the direct-path insert /*+ APPEND */ on all inserts :

  • the direct path insert writes data above the high water mark for the table, ignoring any free space on the freelists, just as SQL Loader direct path load will. If you delete many of the rows in a table and then INSERT /*+ APPEND */ into it, you will not be reusing any of that space.
  • you must commit after a successful direct-path insert before reading from that table in that transaction. This could put commits into your transactions where they don't really belong
  • Only one session at a time can direct-path insert into a table. All other modification are blocked. This operation serializes (but you can do parallel direct-path inserts from a single session)

Setting Up for Direct Path Loads

To prepare the database for direct path loads, you must run the setup script, catldr.sql, to create the necessary views. You need only run this script once for each database you plan to do direct loads to. You can run this script during database installation if you know then that you will be doing direct loads.





Discover More
Card Puncher Data Processing
Oracle - PL/SQL - DML error logging

LOG ERRORS handles errors quickly and simplifies batch loading. When you need to load millions of rows of data into a table, the most efficient way is usually to use an INSERT, UPDATE, or MERGE statement...
Card Puncher Data Processing
Oracle Database - Create table as select (CTAS)

A CTAS operation does a direct path insert (a.k.a. direct load)
Card Puncher Data Processing
Oracle Database - Data Loading Hint

The PL/SQL data loading procedures use the following HINTS for performance. They are /+ APPEND NOLOGGING PARALLEL / Append: - When “Append” hint is used in a PL/SQL INSERT statement, Oracle will...
Card Puncher Data Processing
Oracle Database - How can we load a large number of rows into an indexed existing table ?

If you have to perform data load into a or data mart, you must skip the index to minimize the generation of redo log, such as : set the indexes to the state. They are not dropped but just setting...
Card Puncher Data Processing
Oracle Database - How to bypass the redo log generation ?

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...
Card Puncher Data Processing
Oracle Database - Index (indices)

in Oracle Create Statement for columns table: The LOGGING/NOLOGGING setting determines if operations are...
Card Puncher Data Processing
Oracle Database - Mode Archive Log / Archiving

Oracle Database lets you save filled groups of files to one or more offline destinations, known collectively as the archived , or more simply thearchive log. The process of turning redo log files into...
Card Puncher Data Processing
Oracle Database - NOLOGGING

A table of a statement marked as NOLOGGING will bypass the generation. direct path insert The operation will not be logged and then you prevent the redo log to be generated. It's not in the...
Card Puncher Data Processing
Oracle Database - Partition Exchange for Data Movement / Data Load

One of the most challenging aspects of data warehouse administration is the development of ETL (extract, transform, and load) processes that load data from OLTP systems into data warehouse databases. ...
Card Puncher Data Processing
Oracle Database - Redo Size statistics

The redo size statistics shows how much redo log your statement generated when executed. The redo size statistics is most useful when judging the efficiency of large bulk operations with statements...



Share this page:
Follow us:
Task Runner