使用Direct-Path INSERT插入数据到表中

Inserting Data Into Tables Using Direct-Path INSERT

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

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

• During direct-path INSERT operations, the database 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.

Further, the data can be inserted either in serial mode, where one process executes the statement, or parallel mode, where multiple processes work together simultaneously to run a single SQL statement. The latter is referred to as parallel execution.

This section discusses one aspect of inserting data into tables. Specifically, using the direct-path form. of the INSERT statement. It contains the following topics:

• Advantages of Using Direct-Path INSERT

• Enabling Direct-Path INSERT

• How Direct-Path INSERT Works

• Specifying the Logging Mode for Direct-Path INSERT

• Additional Considerations for Direct-Path INSERT

  Note:

  Only a few details and examples of inserting data into tables are included in this book. Oracle documentation specific to data warehousing and application development provide more extensive information about inserting and manipulating data in tables. For example:

  • Oracle Database Data Warehousing Guide

  • Oracle Database Application Developer's Guide - Fundamentals

  • Oracle Database Application Developer's Guide - Large Objects

Advantages of Using 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.

• Direct-path INSERT must be used if you want to store the data in compressed form. using table compression.

Enabling Direct-Path INSERT

You can implement direct-path INSERT operations by using direct-path INSERT statements, inserting data in parallel mode, or by using the Oracle SQL*Loader utility in direct-path mode. Direct-path inserts can be done in either serial or parallel mode.

To activate direct-path INSERT in serial mode, you must specify 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.

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:

  ALTER SESSION { ENABLE | FORCE } PARALLEL DML;
  
  

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

Notes:

• 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 Oracle Database SQL Reference.

• There are some additional restrictions for using direct-path INSERT. These are listed in the Oracle Database SQL Reference.

See Also:

Oracle Database Performance Tuning Guide  for more information on using hints

How Direct-Path INSERT Works

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

Serial Direct-Path INSERT into Partitioned or Non-partitioned 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 Non-partitioned 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.

Specifying the Logging Mode for Direct-Path INSERT

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   FOR CE   LOGGING  mode, then direct path   INSERT  always logs, regardless of the logging setting.

Direct-Path INSERT with Logging

In this mode, Oracle Database performs full redo logging for instance and media recovery. If the database is in ARCHIVELOG mode, then you can archive 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 Database 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.

Additional Considerations for Direct-Path INSERT

The following are some additional considerations when using direct-path INSERT.

Index Maintenance with Direct-Path INSERT

Oracle Database performs index maintenance at the end of direct-path INSERT operations on tables (partitioned or non-partitioned) 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.

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 non-partitioned tables requires even more space, because it creates a temporary segment for each degree of parallelism. If the non-partitioned table is not in a locally managed tablespace in automatic segment-space management 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.

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, the database 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.

文章来源：

Oracle® Database Administrator's Guide 10g Release 2 (10.2) Part Number B14231-02

1.Direct-Path INSERT的使用
通过以下两种方式可以使用Direct-Path INSERT:
1).insert /*+APPEND*/ into tab1 select * from tab;
或者
insert into tab1 select /*+APPEND*/ from tab;
2).create table tab1 as select /*+APPEND*/ from tab;

2.Direct-Path INSERT插入数据的优势
1).支持并行模式插入数据，即使目标表不是分区表，并行模式也能插入到临时段中，最后将临时段的数据合并到目标表的永久段。可以执行parallel提示，修改会话并行属性来改变SQL语句的并行度。
2).在高水位线以上插入数据库，不会使用现有段存在的自由空间，这样也能更快的插入数据。
3).在执行过程中会忽略索引的存在，索引是在最后来维护，如果是并行INSERT数据，那么索引也是并行维护。整个操作会保证原子性，sql*loader也可以采用Direct-Path的方式插入数据，但sql*loader工具并不保证原子性。
4).可以使用NOLOGGING来减少INSERT执行过程中Redo和UNDO的生成，这样也可以一定程度提高INSERT的性能，但一定要注意 使用NOLOGGING操作完成后要执行一次全库备份，避免在执行介质恢复期间某些区间无法恢复。使用传统的插入方式会记录大量的日志。

传统的插入方式会使用到自由空间，且会随时维护索引。当执行COMMIT提交之后，高水位线才会发生变化，之后用户才能看到插入的数据。

综上所述，可以执行类似如下的SQL来最大程度提高Direct-Path INSERT的性能：
insert /*+APPEND NOLOGGING PARALLEL(4,2)*/ into tab1 select * from tab;
        从10.2.0.4在AIX平台的实践的经验来看，在RAC环境中，执行类似上面这条Direct-Path INSERT语句，Oracle一共创建了65个会话来插入数据，其中实例1有15个会话，实例2有50个会话，由于会话较多，所以在执行过程中可能会收到ORA-04030的报错，通过PARALLEL(4,2)将会话分散到不同的实例，这样出错的几率要小一些。这里再次证明Direct-Path INSERT是并行插入，而传统的插入方式是非并行插入，所以不会收到ORA-04030的报错。

3.Direct-Path INSERT需要注意的问题
1).由于Direct-Path INSERT操作是在高水位线以上插入数据，所以需要更多的空间，如果在并行模式执行Direct-Path INSERT操作，可能还需要更多的临时表空间。
2).在Direct-Path INSERT期间会以排他模式锁定表，并行对表的DML操作都不能执行。
3).如果使用NOLOGGING方式Direct-Path INSERT插入数据，在插入执行完成后一定执行一次全库备份。

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

转载于:http://blog.itpub.net/23135684/viewspace-710982/