Conventional-pathinsert(传统路径插入)

前面我们已经说过了Direct-path INSERT，现在来说一下ConventionalINSERT。文章来源Oracle? Database Administrator's Guide11 g Release 2 (11.2)” Conventional and Direct-Path INSERT You can use the INSERT statement to insert data into a table, p

前面我们已经说过了Direct-path INSERT，现在来说一下Conventional INSERT。文章来源"Oracle? Database Administrator's Guide11g Release 2 (11.2)”

Conventional and Direct-Path INSERT

You can use the INSERT statement to insert data into a table, partition, or view in two ways: conventional INSERT and direct-path INSERT. When you issue a conventional INSERT statement, Oracle Database reuses free space in the table into which you are inserting and maintains referential integrity constraints. With direct-pathINSERT, the database appends the inserted data after existing data in the table. Data is written directly into data files, bypassing the buffer cache. Free space in the existing data is not reused. This alternative enhances performance during insert operations and is similar to the functionality of the Oracle direct-path loader utility, SQL*Loader. When you insert into a table that has been created in parallel mode, direct-pathINSERT is the default.

##向表，分区或者视图中插入数据，我们可以使用传统路径插入和直接路径插入两种方式。

当使用传统路径插入方式时，数据库会利用目标表中空闲空间(插入时会扫描高水位线以下，如果有空闲空间就利用，如果没有空闲空间就扩展)，并且在插入过程中会维护引用的完整性约束。

当使用直接路径插入时，使用高水位线之上的块。数据绕过buffer cache被直接写入数据文件。目标表中空间空间不被使用。direct-pathINSERT的功能同direct-path loader单元SQL*Loader相似，可以提高insert操作的性能。

当你向一个并行表中插入数据时，默认使用direct-pathINSERT方式。

The manner in which the database generates redo and undo data depends in part on whether you are using conventional or direct-pathINSERT:

##数据库日志产生的方式一定程度上取决于你是使用传统路径还是直接路径查收

Conventional INSERT always generates maximal redo and undo for changes to both data and metadata, regardless of the logging setting of the table and the archivelog and force logging settings of the database.##不论表是否设置了logging模式，数据库是否启用了force logging，数据库是否启用了归档，传统路径插入方式总是会为数据好元数据的变化产生大量的redo和undo

Direct-path INSERT generates both redo and undo for metadata changes, because these are needed for operation recovery. For data changes, undo and redo are generated as follows:##直接路径插入会为元数据的改变而产生redo和undo，因为这些是进行恢复所需要的信息。对于数据的变化，其所产生的redo和undo根据下面的条件来决定：

Direct-path INSERT always bypasses undo generation for data changes.##直接路径插入不会产生undo(因为不需要靠undo来回滚)

If the database is not in ARCHIVELOG or FORCE LOGGING mode, then no redo is generated for data changes, regardless of the logging setting of the table. ##如果数据库没有被设置成归档模式，也没有被设置成force logging模式，那么不会为数据的变化产生日志，除非目标表设置了logging模式

If the database is in ARCHIVELOG mode (but not in FORCE LOGGING mode), then direct-path INSERT generates data redo for LOGGING tables but not for NOLOGGING tables.##如果被设置为归档模式，但是没有被设置我force logging，那么直接路径插入会为指定了logging的表的数据变化产生日志，如果表没有指定logging那么就不产生日志

If the database is in ARCHIVELOG and FORCE LOGGING mode, then direct-path SQL generate data redo for both LOGGING and NOLOGGING tables.##如果数据库处于归档模式，并且设置了force logging，那么不论表是否指定了logging属性，直接路径插入都会为数据变化产生日志

Direct-path INSERT is subject to a number of restrictions. If any of these restrictions is violated, then Oracle Database executes conventional INSERT serially without returning any message, unless otherwise noted:

##Direct-path INSERT有如下一些限制。如果符合下面任何一条，那么数据库会在不给任何反馈信息的情况下自动的采用串行传统路径插入

You can have multiple direct-path INSERT statements in a single transaction, with or without other DML statements. However, after one DML statement alters a particular table, partition, or index, no other DML statement in the transaction can access that table, partition, or index.##

Queries that access the same table, partition, or index are allowed before the direct-pathINSERT statement, but not after it.

If any serial or parallel statement attempts to access a table that has already been modified by a direct-pathINSERT in the same transaction, then the database returns an error and rejects the statement.

The target table cannot be of a cluster.

The target table cannot contain object type columns.

Direct-path INSERT is not supported for an index-organized table (IOT) if it is not partitioned, if it has a mapping table, or if it is reference by a materialized view.

Direct-path INSERT into a single partition of an index-organized table (IOT), or into a partitioned IOT with only one partition, will be done serially, even if the IOT was created in parallel mode or you specify theAPPEND or APPEND_VALUES hint. However, direct-path INSERT operations into a partitioned IOT will honor parallel mode as long as the partition-extended name is not used and the IOT has more than one partition.

The target table cannot have any triggers or referential integrity constraints defined on it.

The target table cannot be replicated.

A transaction containing a direct-path INSERT statement cannot be or become distributed.

You cannot query or modify direct-path inserted data immediately after the insert is complete. If you attempt to do so, anORA-12838 error is generated. You must first issue a COMMIT statement before attempting to read or modify the newly-inserted data.

See Also:

Oracle Database Administrator's Guide for a more complete description of direct-pathINSERT

Oracle Database Utilities for information on SQL*Loader

Oracle Database Performance Tuning Guide for information on how to tune parallel direct-pathINSERT

Using Conventional Inserts to Load Tables

During conventional INSERT operations, the database reuses free space in the table, interleaving newly inserted data with existing data. During such operations, the database also maintains referential integrity constraints. Unlike direct-path INSERT operations, conventional INSERT operations do not require an exclusive lock on the table.

Several other restrictions apply to direct-path INSERT operations that do not apply to conventionalINSERT operations. See Oracle Database SQL Language Reference for information about these restrictions.

You can perform a conventional INSERT operation in serial mode or in parallel mode using theNOAPPEND hint.

The following is an example of using the NOAPPEND hint to perform a conventionalINSERT in serial mode:

INSERT /*+ NOAPPEND */ INTO sales_hist SELECT * FROM sales WHERE cust_id=8890;

The following is an example of using the NOAPPEND hint to perform a conventionalINSERT in parallel mode:

INSERT /*+ NOAPPEND PARALLEL */ INTO sales_hist
   SELECT * FROM sales;

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, submit the following statement:

ALTER SESSION { ENABLE | FORCE } PARALLEL DML;

You must meet at least one of the following requirements:

Specify the parallel attribute for the target table, either at create time or subsequently

Specify the PARALLEL hint for each insert operation

Set the database initialization parameter PARALLEL_DEGREE_POLICY toAUTO