25
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 26, "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 Oracle8i Enterprise Edition. See Getting to Know Oracle8i for more information.

Additional Information: See Oracle8i 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" describes these features. For additional information about the parallelism and partitioning options of direct-load INSERT, see Chapter 26, "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".

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

Additional Information: See Oracle8i 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 (the level at which blocks have never been formatted to receive data) 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 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. (For information about the parallel execution coordinator and servers, see "Process Architecture for Parallel Execution".)

• 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 segment(s) 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).

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 25-1 summarizes these requirements and compares direct-load INSERT with conventional INSERT.

Table 25-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;  

For more information on parallel INSERT statements, see "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 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 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. (See "Discrete Transaction Management".) 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 25-2 compares the LOGGING and NOLOGGING modes for direct-load and conventional INSERT.

Table 25-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

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

All of these SQL statements can be parallelized (see Chapter 26, "Parallel Execution"). 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.

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 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:

• NEXT (the size, in bytes, of the object's next extent to be allocated to the object)

• 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 and/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 option 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.

Storage Calculations

In the following discussion:

• The total volume of data for the object is V.

• The degree of parallelism is DOP.

• The percentage of unused space is P.

Calculating NEXT

The average volume of data per parallel execution server is V/DOP. You want the value of the NEXT storage parameter to be in the following range:

1 MB < value of NEXT < V/DOP

If you set the value of NEXT close to the average volume of data per server, this will result in fewer extents but can result in a significant amount of unused space in the object. The largest amount of unused space occurs when each parallel execution server has a whole unused extent, resulting in the amount of unused space equal to DOP * NEXT. This means that average unused space is (DOP * NEXT)/2. Use the formula (P * V)/100 to determine the amount of unused space you can tolerate. Then, determine an appropriate value for NEXT using the following formula:

NEXT = (2 * P * V)/(100 * DOP)

The value for P should be large enough that extents are at least 1 MB and preferably 20 MB or more if the object is large enough. The value for P should also be large enough to allow reasonably large extents such that the total number of extents stays under 1000, especially with non-locally managed tablespaces.

Calculating PCTINCREASE

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.

Calculating MINIMUM EXTENT

If all objects in the database use similar extent sizes, then it is preferable to set the value of the tablespace option MINIMUM EXTENT close to the value of NEXT. If there are objects that will be able to use small extents, then choosing a smaller value for MINIMUM EXTENT will results in less unused space.

Examples of Storage Calculations

Example 25-1 shows a calculation that yields appropriate storage parameter values and Example 25-2 shows a calculation that yields inappropriate storage parameter values.

Example 25-1 Appropriate Storage Parameter Values for Direct-Load INSERT

V = 500 gigabytes (5 * 10¹¹ bytes)

DOP = 100

P = 5 %

NEXT = (2 * 5 * 5 * 10¹¹)/(100 * 100) = 5 * 10⁷ = 500 MB

• The size of each extent is 500 MB.

• The total number of extents is 1,000.

• The average amount of unused space is 5 % of the total amount of space allocated.

Example 25-2 Inappropriate Storage Parameter Values for Direct-Load INSERT

V = 50 megabytes (5 * 10⁷ bytes)

DOP = 10

P = 5 %

NEXT = (2 * 5 * 5 * 10⁷)/(100 * 10) = 5 * 10⁵ = 0.5 MB

In this case:

• The size of each extent is 0.5 MB.

• The total number of extents is 100.

• The average amount of unused space is 5 % of the total amount of space allocated.

Extents with the size 0.5 MB are too small so a higher value of P would be more appropriate.

Additional Information: Refer to the parallel execution chapter in Oracle8i 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".

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

---