使用Direct-Path INSERT插入資料到表中

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.

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.

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插入資料，在插入執行完成後一定執行一次全庫備份。