22
Direct-Load INSERT

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

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

  See Also: Chapter 23, "Parallel Execution of SQL Statements" for more information about parallel execution INSERT issues Oracle8i Designing and Tuning for Performance 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.

See Also: "Varieties of Direct-Load INSERT Statements" Chapter 23, "Parallel Execution of SQL Statements" for more information about the parallelism and partitioning options of direct-load INSERT

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 Also: "CREATE TABLE ... AS SELECT in Parallel"

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).

See Also: Oracle8i 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.

See Also: Oracle8i SQL Reference for information about the syntax of INSERT ... SELECT statements

Varieties of Direct-Load INSERT Statements

Direct-load INSERT can be performed in the following ways:

• Serially or in parallel

• Into partitioned or nonpartitioned 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. The high water mark is the level at which blocks have never been formatted to receive data. 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 execution server allocates a new temporary segment and inserts data into the temporary segment. When a commit executes, the parallel execution coordinator merges the new temporary segments into the primary table segment.

• Parallel direct-load INSERT into a partitioned table. Each parallel execution server is assigned one or more partitions, with no more than one process working on any given partition. The parallel execution server inserts data beyond the current high water mark of the partition segments assigned to it. When a commit executes, the high water mark of each partition segment is updated by the parallel execution coordinator 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.

See Also: "Process Architecture for Parallel Execution"

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/FORCE PARALLEL DML statement.

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

Table 22-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/FORCE 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 (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 (10); 
ALTER TABLE t_emp PARALLEL (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;  

See Also: "Rules for Parallelizing INSERT ... SELECT"

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 statement.

• Direct-load INSERT with logging. This mode can do full redo logging for instance and media recovery. Logging is the default mode. The database must be in ARCHIVELOG mode for online redo logs to be archived to tape. Otherwise, instance crashes are recoverable, but disk failures are not recoverable.

• 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 data. The user is responsible for backing up the data after a no-logging insert operation in order to be able to perform media recovery.

There is no interaction between no-logging mode and discrete transactions, which always generate redo information. Discrete transactions can be issued against tables that use the no-logging attribute.

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 22-2 compares the LOGGING and NOLOGGING modes for direct-load and conventional INSERT.

Table 22-2 Summary of LOGGING and NOLOGGING clauses

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

See Also: "Discrete Transaction Management"

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

• INSERT, UPDATE, and DELETE on LOBs in NOCACHE NOLOGGING mode stored out of line

All of these SQL statements can be parallelized. They can execute in logging or no-logging mode for both serial and parallel execution.

Other SQL statements are unaffected by the NOLOGGING attribute of the schema object. For example, the following SQL statements are unaffected by NOLOGGING mode: UPDATE and DELETE (except on some LOBs, as noted above), conventional path INSERT, and various DDL statements not listed above.

See Also: Chapter 23, "Parallel Execution of SQL Statements"

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, then 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 execution servers 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 above 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 execution server 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.)

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

When you are doing a parallel INSERT for a nonpartitioned table that is not locally managed and is not in automatic mode, modifying the values of the following parameters allows you to provide sufficient storage for temporary segments without wasting space on segments that are larger than you need:

PCTINCREASE	the percentage by which the third and subsequent extents grow over the preceding extent
MINIMUM EXTENT	controls free space fragmentation in the tablespace by ensuring that every used or free extent size in a tablespace is at least as large as, and is a multiple of, the value you specify

Choose values for these parameters such that:

• The size of each extent is not too small (no less than 1 MB). This affects the total number of extents in the object.

• The size of each extent is not so large that the parallel INSERT results in wasting space on segments that are larger than necessary.

You can change the values of the NEXT and PCTINCREASE parameters with the STORAGE clause of the ALTER TABLE statement. You can change the value of the MINIMUM EXTENT parameter with the ALTER TABLESPACE statement. After performing the parallel DML statement, you can change the values of the NEXT, PCTINCREASE, and MINIMUM EXTENT parameters back to settings appropriate for non-parallel operations.

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.

See Also: "Lock and Enqueue Resources for Parallel DML"

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.

See Also: "Restrictions on Parallel DML"