21
Direct-Load INSERT

The translator of Homer should above all be penetrated by a sense of four qualities of his author: that he is eminently rapid; that he is eminently plain and direct ... both in his syntax and in his words; that he is eminently plain and direct in the substance of his thought, ...; and, finally, that he is eminently noble.

Matthew Arnold: On Translating Homer

This chapter describes the Oracle direct-load INSERT feature for serial or parallel inserts. It also describes the NOLOGGING feature that is available for direct-load INSERT and some DDL statements. This chapter's topics include:

• Introduction to Direct-Load INSERT
• Varieties of Direct-Load INSERT Statements
  • Serial and Parallel INSERT
  • Logging Mode
• Additional Considerations for Direct-Load INSERT
• Restrictions on Direct-Load INSERT

See Chapter 22, "Parallel Execution" for parallel-specific issues.

Attention: The parallel direct-load INSERT feature described in this chapter is available only if you have purchased the Oracle8 Enterprise Edition. See Getting to Know Oracle8 and the Oracle8 Enterprise Edition for more information.

Additional Information: See Oracle8 Tuning for information on how to tune parallel direct-load INSERT.

Introduction to Direct-Load INSERT

Direct-load INSERT enhances performance during insert operations by formatting and writing data directly into Oracle datafiles, without using the buffer cache. This functionality is similar to that of the Direct Loader utility (SQL*Loader).

Direct-load INSERT appends the inserted data after existing data in a table; free space within the existing data is not reused. Data can be inserted into partitioned or nonpartitioned tables, either in parallel or serially.

Several options of direct-load INSERT exist with respect to parallelism, table partitioning, and logging. "Varieties of Direct-Load INSERT Statements" on page 21-3 describes these features. For additional information about the parallellism and partitioning options of direct-load INSERT, see Chapter 22, "Parallel Execution".

Advantages of Direct-Load INSERT

A major benefit of direct-load INSERT is that you can load data without logging redo or undo entries, which improves the insert performance significantly. Both serial and parallel direct-load INSERT have this performance advantage over conventional path INSERT.

With the conventional path INSERT, in contrast, free space in the object is reused and referential integrity can be maintained. The conventional path for insertions cannot be parallelized.

Comparison with CREATE TABLE ... AS SELECT

With direct-load INSERT, you can insert data into existing tables instead of having to create new tables. Direct-load INSERT updates the indexes of the table, but CREATE TABLE ... AS SELECT only creates a new table which does not have any indexes. See "CREATE TABLE ... AS SELECT in Parallel" on page 22-26 for more information.

Advantage over Parallel Direct Load (SQL*Loader)

With a parallel INSERT, atomicity of the transaction is ensured. Atomicity cannot be guaranteed if multiple parallel loads are used. Also, parallel load could leave some table indexes in an "Unusable" state at the end of the load if errors occurred while updating the indexes. In comparison, parallel INSERT atomically updates the table and indexes (that is, it rolls back the statement if errors occur while updating the index).

Additional Information: See Oracle8 Utilities for information about parallel load.

INSERT ... SELECT Statements

Direct-load INSERT (serial or parallel) can only support the INSERT ... SELECT syntax of an INSERT statement, not the INSERT... values syntax. The parallelism for INSERT ... SELECT is determined from either parallel hints or parallel table definition clauses.

Additional Information: See Oracle8 SQL Reference for information about the syntax of INSERT ... SELECT statements.

Varieties of Direct-Load INSERT Statements

Direct-load INSERT can be performed either:

• serially or in parallel
• into nonpartitioned or partitioned tables
• with or without logging of redo data

Serial and Parallel INSERT

Direct-load INSERT can be done on partitioned or nonpartitioned tables, and it can be done either serially or in parallel.

• Serial direct-load INSERT into a nonpartitioned or partitioned table. Data is inserted beyond the current high water mark of the table segment or each partition segment. When a commit executes, the high water mark is updated to the new value, making the data visible to others.
• Parallel direct-load INSERT into a nonpartitioned table. Each parallel server process allocates a new temporary segment and inserts data into the temporary segment. When a commit executes, the coordinator process merges the new temporary segments into the primary table segment. (For information about the coordinator process and parallel server processes, see "Process Architecture for Parallel Execution" on page 22-5.)
• Parallel direct-load INSERT into a partitioned table. Each parallel server process is assigned one or more partitions, with no more than one process working on any given partition. The parallel server process inserts data beyond the current high water mark of the partition segment(s) assigned to it. When a commit executes, the high water mark of each partition segment is updated by the coordinator process to the new value, making the data visible to others.

In all the cases, the bumping of the high water mark or merging of the temporary segment is delayed until commit is issued, because this action immediately makes the data visible to other processes (that is, it commits the insert operation).

Specifying Serial or Parallel Direct-Load INSERT

The APPEND hint is required for using serial direct-load INSERT. Parallel direct-load INSERT requires either a PARALLEL hint in the statement or a PARALLEL clause in the table definition; the APPEND hint is optional. Parallel direct-load INSERT also requires parallel DML to be enabled with the ALTER SESSION ENABLE PARALLEL DML statement.

Table 21-1 summarizes these requirements and compares direct-load INSERT with conventional INSERT.

Table 21-1 Summary of Serial and Parallel INSERT ... SELECT Statements

Insert Type	Serial	Parallel
Direct-load INSERT	Yes: requires APPEND hint in SQL statement	Yes: requires ALTER SESSION ENABLE PARALLEL DML table PARALLEL attribute or statement PARALLEL hint (an APPEND hint is optional)
Conventional INSERT	Yes (default)	No

Examples of Serial and Parallel Direct-Load INSERT

You can specify serial direct-load INSERT with the APPEND hint, for example:

INSERT /*+ APPEND */ INTO emp 
   SELECT * FROM t_emp; 
COMMIT; 

You can specify parallel direct-load INSERT by setting the PARALLEL attribute of the table into which rows are inserted, for example:

ALTER TABLE emp PARALLEL (DEGREE 10); 
ALTER SESSION ENABLE PARALLEL DML; 
INSERT INTO emp 
   SELECT * FROM t_emp; 
COMMIT; 

You can also specify parallelism for the SELECT operation by setting the PARALLEL attribute of the table from which rows are selected:

ALTER TABLE emp PARALLEL (DEGREE 10); 
ALTER TABLE t_emp PARALLEL (DEGREE 10); 
ALTER SESSION ENABLE PARALLEL DML; 
INSERT INTO emp 
   SELECT * FROM t_emp; 
COMMIT; 

The PARALLEL hint for an INSERT or SELECT operation takes precedence over a table's PARALLEL attribute. For example, the degree of parallelism in the following INSERT ... SELECT statement is 12 regardless of whether the PARALLEL attributes are set for the EMP and T_EMP tables:

ALTER SESSION ENABLE PARALLEL DML; 
INSERT /*+ PARALLEL(emp,12) */ INTO emp 
   SELECT /*+ PARALLEL(t_emp,12) */ * FROM t_emp;
COMMIT;  

For more information on parallel INSERT statements, see "Rules for Parallelizing INSERT ... SELECT" on page 22-20.

Logging Mode

Direct-load INSERT operations can be done with or without logging of redo information. You can specify no-logging mode for the table, partition, or index into which data will be inserted by using an ALTER TABLE, ALTER INDEX, or ALTER TABLESPACE command.

• Direct-load INSERT with logging. This mode does full redo logging for instance and media recovery. Logging is the default mode.
• Direct-load INSERT with no-logging. In this mode, data is inserted without redo or undo logging. (Some minimal logging is still done for marking new extents invalid, and dictionary changes are always fully logged.) When applied during media recovery, the extent invalidation records mark a range of blocks as logically corrupt, since the redo data is not logged.

The no-logging mode improves performance because it generates much less log. The user is responsible for backing up the data after a no-logging insert operation in order to be able to perform media recovery.

Note: Logging/no-logging mode is not a permanent attribute of the table, partition, or index. After the database object inserted into has been populated with data and backed up, you can set its status to logging mode so that subsequent changes will be logged.

Table 21-2 compares the LOGGING and NOLOGGING modes for direct-load and conventional INSERT.

Table 21-2 Summary of LOGGING and NOLOGGING Options

Insert Type	LOGGING	NOLOGGING
Direct-load INSERT	Yes: recoverability requires ARCHIVELOG database mode	Yes: requires NOLOGGING attribute for tablespace, table, partition, or index
Conventional INSERT	Yes (default): recoverability requires ARCHIVELOG database mode	No

Examples of No-Logging Mode

You can specify no-logging mode for direct-load INSERT by setting the NOLOGGING attribute of the table into which rows are inserted, for example:

ALTER TABLE emp NOLOGGING; 
ALTER SESSION ENABLE PARALLEL DML; 
INSERT /*+ PARALLEL(emp,12) */ INTO emp 
   SELECT /*+ PARALLEL(t_emp,12) */ * FROM t_emp;
COMMIT;  

You can also set the NOLOGGING attribute for a partition, tablespace, or index; for example:

ALTER TABLE emp MODIFY PARTITION emp_lmnop NOLOGGING; 

ALTER TABLESPACE personnel NOLOGGING; 

ALTER INDEX emp_ix NOLOGGING; 

ALTER INDEX emp_ix MODIFY PARTITION eix_lmnop NOLOGGING; 

SQL Statements That Can Use No-Logging Mode

Although you can set the NOLOGGING attribute for a table, partition, index, or tablespace, no-logging mode does not apply to every operation performed on the schema object for which you set the NOLOGGING attribute.

Only the following operations can make use of no-logging mode:

• direct load (SQL*Loader)
• direct-load INSERT
• CREATE TABLE ... AS SELECT
• CREATE INDEX
• ALTER TABLE ... MOVE PARTITION
• ALTER TABLE ... SPLIT PARTITION
• ALTER INDEX ... SPLIT PARTITION
• ALTER INDEX ... REBUILD
• ALTER INDEX ... REBUILD PARTITION

All of these SQL statements can be parallelized (see Chapter 22, "Parallel Execution"). They can execute in logging or no-logging mode for both serial and parallel execution.

Other SQL statements (such as UPDATE, DELETE, conventional path INSERT, and various DDL statements not listed above) are unaffected by the NOLOGGING attribute of the schema object.

Default Logging Mode

If the LOGGING or NOLOGGING clause is not specified, the logging attribute of the table, partition, or index defaults to the logging attribute of the tablespace in which it resides.

For LOBs, if the LOGGING or NOLOGGING clause is omitted, then:

• if CACHE is specified, LOGGING is used (because LOBs cannot have CACHE NOLOGGING)
• otherwise, the default is obtained from the tablespace in which the LOB value resides.

Additional Considerations for Direct-Load INSERT

This section describes index maintenance, space allocation, and data locks for direct-load INSERT.

Index Maintenance

For direct-load INSERT on nonpartitioned tables or partitioned tables that have local or global indexes, index maintenance is done at the end of the INSERT operation. This index maintenance is performed by the parallel server processes for parallel direct-load INSERT or by the single process for serial direct-load INSERT on partitioned or nonpartitioned tables.

If your direct-load INSERT modifies most of the data in a table, you can avoid the performance impact of index maintenance by dropping the index before the INSERT and then rebuilding it afterwards.

Space Considerations

Direct-load INSERT requires more space than conventional path INSERT, because direct-load INSERT ignores existing space in the free lists of the segment. For parallel direct-load INSERT into nonpartitioned tables, free blocks below the high water mark of the table segment are also ignored. Additional space requirements must be considered before using direct-load INSERT.

Parallel direct-load INSERT into a nonpartitioned table creates temporary segments - one segment for each degree of parallelism. For example, if you use parallel INSERT into a nonpartitioned table with the degree of parallelism set to four, then four temporary segments are created.

Each parallel server process first inserts its data into a temporary segment, and finally the data in all of the temporary segments is appended to the table. (This is the same mechanism as CREATE TABLE ... AS SELECT.)

To provide sufficient storage for temporary segments, without wasting space on segments that are larger than you need, you should specify appropriate values for the storage parameters NEXT and PCTINCREASE for a nonpartitioned table into which you want to do a parallel INSERT. You can change the values of these parameters with the STORAGE option of the ALTER TABLE statement. After performing the parallel DML statement, you can change the NEXT and PCTINCREASE storage parameters back to settings appropriate for non-parallel operations.

Note: The PCTINCREASE storage parameter can produce very large temporary segments, unless it is set to 0. To avoid running out of space while doing parallel DML, make sure that PCTINCREASE is set to 0.

For parallel INSERT into a partitioned table, however, no temporary segments are created. Each parallel server process simply inserts its data into a partition above the high water mark.

Additional Information: Refer to the parallel execution chapter in Oracle8 Tuning for more discussion of space management.

Locking Considerations

In direct-load INSERT, exclusive locks are obtained on the table (or on all the partitions of a partitioned table) precluding any concurrent insert, update, or delete on the table. Concurrent queries, however, are supported and will see only the data in the table before the INSERT began. These locks also prevent any concurrent index creation or rebuild operations. This must be taken into account before using direct-load INSERT because it affects table concurrency. For more information, see "Lock and Enqueue Resources for Parallel DML" on page 22-36.

Restrictions on Direct-Load INSERT

The restrictions on direct-load INSERT are the same as those imposed on direct-path parallel loading with SQL*Loader, because they use the same underlying mechanism. In addition, the general parallel DML restrictions also apply to direct-load INSERT.

Serial and parallel direct-load INSERT have the following restrictions:

• A transaction can contain multiple direct-load INSERT statements (or both direct-load INSERT statements and parallel UPDATE or DELETE statements), but after one of these statements modifies a table, no other SQL statement in the transaction can access the same table.
  • Queries that access the same table are allowed before the direct-load INSERT statement, but not after.
  • Any serial or parallel statements attempting to access a table that has already been modified by a direct-load INSERT (or parallel DML) within the same transaction are rejected with an error message.
• If the initialization parameter ROW_LOCKING = INTENT, then inserts cannot be performed by the direct-load path.
• Direct-load INSERT does not support referential integrity.
• Triggers are not supported for direct-load INSERT operations.
• Replication functionality is not supported for direct-load INSERT.
• Direct-load INSERT cannot occur on tables with object columns or LOB columns, or on index-organized tables.
• A transaction involved in a direct-load INSERT operation cannot be or become a distributed transaction.
• Clustered tables are not supported.

Violations of the restrictions will cause the statement to execute serially, using the conventional insert path, without warnings or error messages. An exception is the restriction on statements accessing the same table more than once in a transaction, which can cause error messages.

For example, if triggers or referential integrity are present on the table, then the APPEND hint will be ignored when you try to use direct-load INSERT (serial or parallel), as well as the PARALLEL hint or clause, if any.

For more information about the general restrictions on parallel DML (including parallel INSERT), see "Restrictions on Parallel DML" on page 22-37.

---