direct path insert 以及append参数

||||||||||||||||||||||||||

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.

 

 

详细情况参考：

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:

  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.

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.

 

||||||||||||||||||||||||

 

//////////////////////

会在一定程度上造成空间浪费
请看oracle文挡中的描述：
APPEND
The APPEND hint lets you enable direct-path INSERT if your database is runningin serial mode. Your database is in serial mode if you are not using EnterpriseEdition. Conventional INSERT is the default in serial mode, and direct-pathINSERT is the default in parallel mode.
In direct-path INSERT, data is appended tothe end of the table, rather than using existing space currently allocated tothe table. As a result, direct-path INSERT can be considerably faster than conventionalINSERT.

SQL语句中的优化提示
APPEND : Only valid for INSERT .. SELECT. Allows INSERT to work like directload or to perform parallel insert.

/////////////////////////

1. append方式添加记录对insert into ... values语句不起作用。
2. 以append方式批量插入的记录，其存储位置在hwm 之上，即使hwm之下存在空闲块也不能使用。
3. 以append方式插入记录后，要执行commit，才能对表进行查询。否则会出现错误：
ORA-12838: 无法在并行模式下修改之后读/修改对象
4. 在归档模式下，要把表设置为nologging，然后以append方式批量添加记录，才会显著减少redo数量。在非归档模式下，不必设置表的 nologging属性，即可减少redo数量。如果表上有索引，则append方式批量添加记录，不会减少索引上产生的redo数量，索引上的redo 数量可能比表的redo数量还要大
5. 同 nologging 配合会更快的,使用新的BLOCK 而不使用FREELIST中的块 增加插入速度,使用这个hint可以将数据使用直接路径插入到表的高水线之后，由于是连续的没有使用的空间，所以插入速度快。就是说直接插入,减少了搜索块的时间.

||||||||||||||||||||||||||||||||||

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（就像电信里存详单的表），这样就不存在空间浪费.否则空间浪费可能不可避免。

 

 

 

 

 

||||||||||||||||||||||||

(1)、Oracle插入数据有两种方式：
a、常规插入：重新使用table中的自由空间，在已有数据中插入新数据；维护引用完整性约束。
b、Direct-Path插入：在表中已有数据之后插入新数据；数据直接插入数据文件，绕过buffer cache；已有数据中的自由空间没有被重新利用；忽略了引用完整性约束。

 

（2）连续模式：一个进程执行sql语句；
     并行模式：多个进程同时执行一个sql语句，即并行执行。
 
（3）Direct-Path插入的优点：
1、可以disable redo log和undo log；而常规插入却不可以如此，并且重用自由空间和维护引用完整性。
2、通过CREATE TABLE...AS SELECT 语句可以从现有表中创建新表，使用Direct-Path插入可以在插入时更新定义在目标表上的任意索引。
3、Direct-Path插入能保证事务的原子性，即使在并行模式下。使用SQL*Loader在并行模式加载中不能保证原子性。
4、当并行Direct-Path加载发生错误时，索引被标志成UNUSABLE。
5、如果想使用表压缩的压缩格式存储数据，必须使用Direct-Path插入。
 
 
（4）使用Direct-Path插入：
 在连续模式中，在Insert语句中指定“APPEND”提示，在INSERT关键字之后，或者在INSERT子查询中的SELECT关键字之后。
在并行DML模式中，默认的就是DIRECT-PATH插入，为了运行并行DML模式，必须满足以下条件：
a、必须是Oracle企业版；
b、必须在session中使并行DML生效，执行以下sql语句：
ALTER SESSION { ENABLE | FORCE } PARALLEL DML;
c、必须指定table的并行属性，在创建的时候或者其他时候，或者在insert操作时使用“PARALLEL”提示。
    为了使Direct-Path Insert模式失效，在INSERT语句中指定“NOAPPEND”提示，覆盖并行DML模式。
 
（5）、Direct-Path INSERT 是如何工作的
 
在分区表和非分区表都可以使用Direct-Path INSERT连续Direct-Path INSERT到分区和非分区表
  单个进程插入数据到高水位标志之上，当执行完commit之后，高水位标志得到更新。
 
并行Direct-Path INSERT到分区表
  类似于serial Direct-Path INSERT，每个并行操作分配给一个或者多个分区，每个并行操作插入数据到各自的分区段的高水位标志之上，commit之后，用户就能看到更新的数据。
 
并行Direct-Path INSERT到非分区表
  每个并行执行分配一个新的临时段，并插入数据到临时段。当commit运行后，并行执行协调者合并新的临时段到主表段，用户就能看到更新的数据。
 
为Direct-Path INSERT指定的Log模式
   Direct-Path INSERT可以使用Log或者不使用Log。
 
（6）、其他需要注意的地方：
    索引维护：Oracle在Direct-Path INSERT 操作末尾，对具有索引的表执行索引维护，这样就避免了在drop掉索引后，再rebuild。
    使用的空间：Direct-Path INSERT比常规的插入需要更多的空间。因为它将数据插入在高水位之上。并行插入非分区表需要更多的空间，因为它需要为每一个并行创建临时段。
    锁：在插入期间，数据库在表上获得排他锁，用户不能在表上执行并行插入、更新或者删除操作，并行的索引创建和build也不被允许。但却可以并行查询，但查询返回的是插入之前的结果集。

|

全部脚印 不留脚印 留下脚印: