Conventional-pathinsert(传统路径插入) - 数据库编程

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.

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

Conventional-pathinsert(传统路径插入)(二)

Using Conventional Inserts to Load Tables