|Oracle9i Database Concepts
Release 2 (9.2)
Part Number A96524-01
This chapter describes the Oracle direct-path
INSERT feature for serial or parallel inserts. It also describes the
NOLOGGING feature available for direct-path
INSERT and some DDL statements. This chapter's topics include:
The parallel direct-path
Oracle inserts data into a table in one of two ways:
You can implement direct-path
INSERT operations by using direct-path
INSERT statements or by using Oracle's direct-path loader utility, SQL*Loader. This section discusses direct-path
The following are performance benefits of direct-path
INSERT, you can disable the logging of redo and undo entries. Conventional insert operations, in contrast, must always log such entries, because those operations reuse free space and maintain referential integrity.
SELECTstatement. By creating the table and then using direct-path
INSERToperations, you update any indexes defined on the target table during the insert operation. The table resulting from a
SELECTstatement, in contrast, does not have any indexes defined on it; you must define them later.
INSERToperations ensure atomicity of the transaction, even when run in parallel mode. Atomicity cannot be guaranteed during parallel direct-path loads (using SQL*Loader).
UNUSABLEat the end of the load. Parallel direct-path
INSERT, in contrast, rolls back the statement if errors occur during index update.
When you are inserting in parallel DML mode, direct-path
INSERT is the default. In order to run in parallel DML mode, the following requirements must be met:
PARALLELhint for each insert operation.
To disable direct-path
INSERT, specify the
NOAPPEND hint in each
INSERT statement. Doing so overrides parallel DML mode.
Oracle9i Database Performance Tuning Guide and Reference for more information on using hints
When you are inserting in serial mode, you must activate direct-path
INSERT by specifying the
APPEND hint in each insert statement, either immediately after the
INSERT keyword, or immediately after the
SELECT keyword in the subquery of the
You can use direct-path
INSERT on both partitioned and nonpartitioned tables.
The single process inserts data beyond the current high water mark of the table segment or of each partition segment. (The high-water mark is the level at which blocks have never been formatted to receive data.) When a
COMMIT runs, the high-water mark is updated to the new value, making the data visible to users.
This situation is analogous to serial direct-path
INSERT. Each parallel execution server is assigned one or more partitions, with no more than one process working on a single partition. Each parallel execution server inserts data beyond the current high-water mark of its assigned partition segment(s). When a
COMMIT runs, the high-water mark of each partition segment is updated to its new value, making the data visible to users.
Each parallel execution server allocates a new temporary segment and inserts data into that temporary segment. When a
COMMIT runs, the parallel execution coordinator merges the new temporary segments into the primary table segment, where it is visible to users.
INSERT lets you choose whether to log redo and undo information during the insert operation.
LOBstorage at create time (in a
CREATEstatement) or subsequently (in an
NOLOGGINGat these times:
LOBstorage defaults to
LOGGINGif you specify
LOBstorage. If you do not specify
CACHE, then the logging attributes defaults to that of the tablespace in which the
In this mode, Oracle performs full redo logging for instance and media recovery. If the database is in
ARCHIVELOG mode, then you can archive online redo logs to tape. If the database is in
NOARCHIVELOG mode, then you can recover instance crashes but not disk failures.
In this mode, Oracle inserts data without redo or undo logging. (Some minimal logging is done to mark new extents invalid, and data dictionary changes are always logged.) This mode improves performance. However, if you subsequently must perform media recovery, the extent invalidation records mark a range of blocks as logically corrupt, because no redo data was logged for them. Therefore, it is important that you back up the data after such an insert operation.
Oracle performs index maintenance at the end of direct-path
INSERT operations on tables (partitioned or nonpartitioned) that have indexes. This index maintenance is performed by the parallel execution servers for parallel direct-path
INSERT or by the single process for serial direct-path
INSERT. You can avoid the performance impact of index maintenance by dropping the index before the
INSERT operation and then rebuilding it afterward.
INSERT requires more space than conventional-path
INSERT, because direct-path
INSERT does not use existing space in the free lists of the segment.
All serial direct-path
INSERT operations as well as parallel direct-path
INSERT into partitioned tables insert data above the high-water mark of the affected segment. This requires some additional space.
INSERT into nonpartitioned tables requires even more space, because it creates a temporary segment for each degree of parallelism. If the nonpartitioned table is not in a locally managed tablespace in automatic mode, you can modify the values of the
PCTINCREASE storage parameter and
EXTENT tablespace parameter to provide sufficient (but not excess) storage for the temporary segments. Choose values for these parameters so that:
INSERTresults in wasted space on segments that are larger than necessary.
Oracle9i SQL Reference for information on setting these parameters
After the direct-path
INSERT operation is complete, you can reset these parameters to settings more appropriate for serial operations.
INSERT, Oracle obtains exclusive locks on the table (or on all partitions of a partitioned table). As a result, users cannot perform any concurrent insert, update, or delete operations on the table, and concurrent index creation and build operations are not permitted. Concurrent queries, however, are supported, but the query will return only the information before the insert operation.