Direct-Path INSERT

insert into 的时候加append 提示只是在表的HWM之上直接重新分配新的数据块，而不是在freelist中查找可以insert 的块与产生不产生回滚信息没有关系

做任何的DML操作都会有回滚的，如果是批量的insert ,可以加append提示和nologging 参数来提高速度

Direct-path insert是直接追加high water mark of the table后，数据是通过buffer cache直接写到datafiles中的啊，这样表中现存block中的Free space就不能被利用，如果你的表不存在频繁的delete（就像电信里存详单的表），这样就不存在空间浪费.否则空间浪费可能不可避免。

注意：表被设置在nologging 状态下，也只有在 direct insert 的时候才生效(nologging不是不生成日志，只是生成的日志比较少而已) ，其他情况下还是会产生正常多的log.

表被设置在nologging 状态下，在 direct insert 的时候产生比较少的Log . 详细解释如下：

Direct-Path INSERT without Logging

详细情况参考：

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:

Introduction to Direct-Path INSERT
Advantages of Direct-Path INSERT
Serial and Parallel Direct-Path INSERT
Direct-Path INSERT Into Partitioned and Nonpartitioned Tables
Direct-Path INSERT and Logging Mode

Additional Considerations for Direct-Path INSERT

Note:

The parallel direct-path INSERT feature described in this chapter is available only if you have purchased the Oracle9i Enterprise Edition. See Oracle9i Database New Features for more information.

See Also:

Chapter 18, "Parallel Execution of SQL Statements" for more information about parallel execution INSERT issues
Oracle9i Data Warehousing Guide

Introduction to Direct-Path INSERT

Oracle inserts data into a table in one of two ways:

During conventional insert operations, Oracle reuses free space in the table, interleaving newly inserted data with existing data. During such operations, Oracle also maintains referential integrity constraints.
During direct-path INSERT operations, Oracle appends the inserted data after existing data in the table. Data is written directly into datafiles, bypassing the buffer cache. Free space in the existing data is not reused, and referential integrity constraints are ignored. These procedures combined can enhance performance.

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

See Also:

Oracle9i Database Utilities for information on direct-path load and SQL*Loader
Oracle9i SQL Reference for a list of direct-path INSERT restrictions

Advantages of Direct-Path INSERT

The following are performance benefits of direct-path INSERT:

During 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.
To create a new table with data from an existing table, you have the choice of creating the new table and then inserting into it, or executing a CREATE TABLE ... AS SELECT statement. By creating the table and then using direct-path INSERT operations, you update any indexes defined on the target table during the insert operation. The table resulting from a CREATE TABLE ... AS SELECT statement, in contrast, does not have any indexes defined on it; you must define them later.
Direct-path INSERT operations ensure atomicity of the transaction, even when run in parallel mode. Atomicity cannot be guaranteed during parallel direct-path loads (using SQL*Loader).
If errors occur during parallel direct-path loads, some indexes could be marked UNUSABLE at the end of the load. Parallel direct-path INSERT, in contrast, rolls back the statement if errors occur during index update.

Serial and Parallel Direct-Path INSERT

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:

You must have Oracle Enterprise Edition installed.
You must enable parallel DML in your session. To do this, run the following statement:
You must specify the parallel attribute for the target table, either at create time or subsequently, or you must specify the PARALLEL hint for each insert operation.

To disable direct-path INSERT, specify the NOAPPEND hint in each INSERT statement. Doing so overrides parallel DML mode.

See Also:

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

Note:

Direct-path INSERT supports only the subquery syntax of the INSERT statement, not the VALUES clause. For more information on the subquery syntax of INSERT statements, see Oracle9i SQL Reference.

Direct-Path INSERT Into Partitioned and Nonpartitioned Tables

You can use direct-path INSERT on both partitioned and nonpartitioned tables.

Serial Direct-Path INSERT into 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.

Parallel Direct-Path INSERT into Partitioned Tables

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.

Parallel Direct-Path INSERT into Nonpartitioned Tables

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.

Direct-Path INSERT and Logging Mode

Direct-path INSERT lets you choose whether to log redo and undo information during the insert operation.

You can specify logging mode for a table, partition, index, or LOB storage at create time (in a CREATE statement) or subsequently (in an ALTER statement).
If you do not specify either LOGGING or NOLOGGING at these times:
- The logging attribute of a partition defaults to the logging attribute of its table.
- The logging attribute of a table or index defaults to the logging attribute of the tablespace in which it resides.
- The logging attribute of LOB storage defaults to LOGGING if you specify CACHE for LOB storage. If you do not specify CACHE, then the logging attributes defaults to that of the tablespace in which the LOB values resides.
You set the logging attribute of a tablespace in a CREATE TABLESPACE or ALTER TABLESPACE statements.

Note:

If the database or tablespace is in FORCE LOGGING mode, then direct path INSERT always logs, regardless of the logging or nologging setting.

Direct-Path INSERT with Logging

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.

Direct-Path INSERT without Logging

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.

See Also:

Oracle9i Backup and Recovery Concepts for recovery information
Oracle9i SQL Reference for information on logging mode in operations other than inserts

Additional Considerations for Direct-Path INSERT

Index Maintenance with Direct-Path INSERT

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.

Space Considerations with Direct-Path INSERT

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

Parallel direct-path 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 NEXT and PCTINCREASE storage parameter and MINIMUM EXTENT tablespace parameter to provide sufficient (but not excess) storage for the temporary segments. Choose values for these parameters so that:

The size of each extent is not too small (no less than 1 MB). This setting affects the total number of extents in the object.
The size of each extent is not so large that the parallel INSERT results in wasted space on segments that are larger than necessary.

See Also:

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.

Locking Considerations with Direct-Path INSERT

During direct-path 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.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/35489/viewspace-85081/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/35489/viewspace-85081/