Direct-PathINSERT (Direct path insertion)

Next, let's take a look at what direct paths are and the advantages and restrictions of using direct paths. The article is taken from "Oracle? DatabaseAdministratorsGuide11gRelease2 (11.2) "(of course, the Chinese annotation is not in the official documentation) ImprovingINSERTPerformancewithDirect-PathINSERTWhenloadinglarge

Next, let's take a look at what direct paths are and the advantages and restrictions of using direct paths. The article is taken from "Oracle? Database Administrator's Guide11 g Release 2 (11.2) "(of course, the Chinese annotation is not in the official documentation) Improving INSERT Performance with Direct-Path INSERT When loading large

Next, let's take a look at what direct paths are and the advantages and restrictions of using direct paths. From" Oracle? Database Administrator's Guide11GRelease 2 (11.2)"(Of course, the Chinese annotation is not in the official documentation) Improving INSERT Performance with Direct-Path INSERT

When loading large amounts of data, you can improve load performance by using direct-pathINSERT.

# When loading a large amount of data, we can use direct-path INSERT to improve processing performance

This section contains:

About Direct-Path INSERT

How Direct-Path INSERT Works

Loading Data with Direct-Path INSERT

Specifying the Logging Mode for Direct-Path INSERT

Additional Considerations for Direct-Path INSERT

About Direct-Path INSERT

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

# There are two ways for Oracle database to insert data into a table (traditional path and direct path ):

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. # use the traditional path method to insert data. The database will use the existing free space in the table. The new and old data are overlapped, and the reference integrity constraints will be maintained during the insertion process.

During direct-path INSERT operations, 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 table is not reused, and referential integrity constraints are ignored. direct-path INSERT can perform significantly better than conventional insert. # use a direct path to insert data, and the database appends data after the existing data in the table (that is, directly use a new block above the high water level line, not like the traditional path Scans idle blocks under the high waterline with the same diameter ). Data is directly written into the data file by bypassing the buffer cache. The free space below the high water level line will not be used, and the integrity constraints of the table will be ignored. Compared with traditional path insertion, direct path insertion efficiency is significantly improved.

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

# The database can insert data in serial mode or in parallel mode, that is, parallel execution

The following are benefits of direct-path INSERT:

# The Advantages of direct-path INSERT are listed below:

During direct-path INSERT, you can disable the logging of redo and undo entries to reduce load time. conventional insert operations, in contrast, must always log such entries, because those operations reuse free space and maintain referential integrity. # When loading data using a direct path, you can disable the generation of redo and undo logs to shorten the loading time. In contrast, loading a traditional path always generates these log entries.

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 ). # The Direct-pathINSERT operation ensures the atomicity of things, even if the parallel mode is used. However, direct-path loads (using SQL * Loader) cannot guarantee the atomicity of things.

When coloring parallel direct-path loads, one notable difference between SQL * Loader andINSERT statements is the following: If errors occur during parallel direct-path loads with SQL * Loader, the load completes, but some indexes cocould be markedUNUSABLE at the end of the load. parallel direct-path INSERT, in contrast, rolls back the statement if errors occur during index update. # When parallel direct- When using path loads, it should be noted that, unlike direct-path INSERT, if an error occurs during parallel direct-path loads data import using SQL * Loader, the data import is complete, however, the index is marked as invalid. In contrast, if Parallel direct-path INSERT fails during Index Update, the transaction will be rolled back.

Note:

A conventional INSERT operation checks for violations of NOTNULL constraints during the insert. therefore, if aNOTNULL constraint is violated for a conventional INSERT operation, then the error is returned during the insert. A direct-pathINSERT operation checks for violations ofNOT NULL constraints before the insert. therefore, if aNOTNULL constraint is violated for a direct-path INSERT operation, Then the error is returned before the insert. # traditional path insertion checks whether non-null constraints are violated during the insertion process. Therefore, if the inserted data violates non-null constraints, an error is reported during the insertion process. Before inserting a direct path, check whether the data violates the non-null constraint. Therefore, if the data violates the non-null constraint, an error is reported before inserting the data. How Direct-Path INSERT Works

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

# Direct-path INSERT can be used for partitioned and non-Partitioned Tables

Serial Direct-Path INSERT into Partitioned or Nonpartitioned Tables

The single process inserts data beyond the current high water mark of the table segment or of each partition segment. (Thehigh-water mark is the level at which blocks have never been formatted to receive data .) when aCOMMIT runs, the high-water mark is updated to the new value, making the data visible to users.

# Use a data block above the high watermark to insert data. After a commit request is submitted, the high watermark is updated to a new value, make the newly inserted data visible to the user (in the same thing before direct-path INSERT is not committed, the inserted Table cannot be dml or query, otherwise, a ORA-12838 error is reported. Other sessions can query the table, but only the data before insertion can be found)

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.

# The parallel mode is similar to the serial mode. Each parallel process is assigned to one or more partitions, but multiple programs do not process one partition.

Parallel Direct-Path INSERT into Nonpartitioned Tables

Each parallel execution server allocates a new temporary segment and inserts data into that temporary segment. when aCOMMIT runs, the parallel execution coordinator merges the new temporary segments into the primary table segment, where it is visible to users.

# A new temporary segment is allocated to each parallel process and data is inserted into the temporary segment. However, when you execute commit, the parallel execution of the scheduling process will merge these temporary segments into the segment where the table is located, and the data is visible to the user.

Loading Data with Direct-Path INSERT

You can load data with direct-path INSERT by using direct-path insert SQL statements, inserting data in parallel mode, or by using the Oracle SQL * Loader utility in direct-path mode. A direct-pathINSERT can be done in either serial or parallel mode.

##

Serial Mode Inserts with SQL Statements

You can activate direct-path INSERT in serial mode with SQL in the following ways:

# Use the following method to activate direct path Loading

If you are inserting Ming an INSERT with a subquery, specify the APPEND hint in each INSERT statement, either immediately after theINSERT keyword, or immediately after theSELECT keyword in the subquery of theINSERT statement. # If you insert data using a subquery, add an append prompt after insert.

If you are inserting Ming an INSERT with the VALUES clause, specify theAPPEND_VALUES hint in eachINSERT statement immediately after theINSERT keyword. direct-pathINSERT with the VALUES clause is best used when there are hundreds of thousands or millions of rows to load. the typical usage scenario is for array inserts using OCI. another usage scenario might be inserts in aFORALL statement in PL/SQL. # if you are using insert with values clause, add APPEND_VALUES after insert.

If you specify the APPEND hint (as opposed to the APPEND_VALUES hint) in anINSERT statement with aVALUES clause, the APPEND hint is ignored and a conventional insert is already med.

# If you use the append prompt instead of the APPEND_VALUES prompt in the insert with the values clause, the append prompt will be ignored and the traditional path insert will be executed.

The following is an example of using the APPEND hint to perform a direct-pathINSERT:

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

The following PL/SQL code fragment is an example of using the APPEND_VALUES hint:

FORALL i IN 1..numrecords  INSERT /*+ APPEND_VALUES */ INTO orderdata   VALUES(ordernum(i), custid(i), orderdate(i),shipmode(i), paymentid(i));COMMIT;

Parallel Mode Inserts with SQL Statements

When you are inserting in parallel mode, direct-path INSERT is the default. However, you can insert in parallel mode using conventionalINSERT by using theNOAPPEND PARALLEL hint.

# If you insert data in parallel mode, the direct path is used by default. However, you can also use the noappend parallel prompt to force data insertion using a traditional path.

To run in parallel DML mode, the following requirements must be met:

# To Run DML in parallel mode, the following conditions must be met:

You must have Oracle Enterprise Edition installed. # You must install oracle Enterprise Edition

You must enable parallel DML in your session. To do this, submit the following statement: # You must enable session-Level parallel DML

ALTER SESSION { ENABLE | FORCE } PARALLEL DML;

You must meet at least one of the following requirements: # at the same time, You must satisfy one of the following conditions:

Specify the parallel attribute for the target table, either at create time or subsequently # Specify parallel attributes for the target table (specified during table creation or after table creation)

Specify the PARALLEL hint for each insert operation # Add an aprallel prompt during insert

Set the database initialization parameter PARALLEL_DEGREE_POLICY toAUTO # Set PARALLEL_DEGREE_POLICY to AUTO

To disable direct-path INSERT, specify the NOAPPEND hint in eachINSERT statement. Doing so overrides parallel DML mode.

# Disable direct path insertion by using the NOAPPEND prompt.

Note:

You cannot query or modify data inserted using direct-path INSERT immediately after the insert is complete. if you attempt to do so, an ORA-12838 error is generated. you must first issue aCOMMIT statement before attempting to read or modify the newly-inserted data. # After direct-path INSERT is complete, you should first execute commit and then query and modify the data in the table. If you query or modify data before submitting it, a ORA-12838 error is reported (note that the situation is in the same session, if you can still query the table in another session, you can insert the previous data when you check the data. However, even in another session, you cannot perform dml operations, because the exclusive lock will be applied to the table during direct path loading)

See Also:

"Using Conventional Inserts to Load Tables"

Oracle Database Performance Tuning Guide for more information on using hints

Oracle Database SQL Language Reference for more information on the subquery syntax ofINSERT statements and for additional restrictions on using direct-pathINSERT

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.

# Direct-path INSERT allows you to choose whether to record redo and undo log information (this indicates whether to write redo and undo logs)

You can specify logging mode for a table, partition, index, or LOB storage at create time (in aCREATE statement) or subsequently (in anALTER statement ).

If you do not specify either LOGGING or NOLOGGING at these times:

The logging attribute of a partition ults to the logging attribute of its table. # If you explicitly specify The log attribute for the partition, The partition inherits the log attribute of The table.

The logging attribute of a table or index defaults to the logging attribute of the tablespace in which it resides. # if no log attribute is specified for a table or index, it inherits The log attribute of the tablespace in which it belongs.

The logging attribute of LOB storage defaults to LOGGING if you specifyCACHE forLOB storage. if you do not specify CACHE, then the logging attributes defaults to that of the tablespace in which theLOB values resides. # The LOB storage depends on the CACHE mode. If it is in the CACHE mode, the default value is the LOGGING attribute. If it is NOCACHE, the LOGGING attribute of the tablespace to which it belongs depends.

You set the logging attribute of a tablespace in a create tablespace orALTERTABLESPACE statements.

Note:

If the database or tablespace is in force logging mode, then direct pathINSERT always logs, regardless of the logging setting. # If the database or tablespace is in FORCELOGGING mode, logs will be recorded for direct path loading no matter how other log attributes are set. Direct-Path INSERT with Logging

In this mode, Oracle Database performs full redo logging for instance and media recovery. if the database is inARCHIVELOG mode, then you can archive redo logs to tape. if the database is inNOARCHIVELOG mode, then you can recover instance crashes but not disk failures.

# In this mode, the database records all logs required for instance and media recovery. If the database is in archive mode, you can archive the logs to the tape. If the database is in non-archive mode, you can restore the instance but cannot restore the media.

Direct-Path INSERT without Logging

In this mode, Oracle Database inserts data without redo or undo logging. instead, the database logs a small number of block range invalidation redo records and periodically updates the control file with information about the most recent direct write.

# In this mode, the database does not record the redo and undo logs during data insertion, but a small number of logs are generated when the invalid status is marked in the new area and the data dictionary is modified, and regularly update the current direct write information in the control file.

Direct-path INSERT without logging improves performance. however, if you subsequently must perform media recovery, the invalidation redo records mark a range of blocks as logically upt, because no redo data was logged for them. therefore, it is important that you back up the data after such an insert operation.

# Direct-path INSERT without logging can improve performance. Then, if you recover the media later, blocks marked as invalid will report logical errors because they are not logged. Therefore, it is necessary to back up data after the direct path.

Beginning with release 11.2.0.2 of Oracle Database, you can significantly improve the performance of unrecoverable direct path inserts by disabling the periodic update of the control files. you do so by setting the initialization parameterDB_UNRECOVERABLE_SCN_TRACKING to FALSE. however, if you perform an unrecoverable direct path insert with these control file updates disabled, you will no longer be able to accurately query the database to determine if any data files are currently unrecoverable.

# Starting from oracle11.2.0.2, If you disable the regular update control file mechanism by setting DB_UNRECOVERABLE_SCN_TRACKING to FALSE when using Direct-path INSERT, the data insertion performance will be greatly improved. However, if you do this, you will not be able to recover the data files you have queried through the database.

See Also:

Oracle Database Backup and Recovery User's Guide for more information about unrecoverable data files

The section "Determining If a Backup Is Required After Unrecoverable Operations" inOracle Data Guard Concepts and Administration

Additional Considerations for Direct-Path INSERT

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

# Other considerations for using direct-path INSERT are listed below:

Compressed Tables

If a table is created with the basic compression, then you must use direct-pathINSERT to compress table data as it is loaded. if a table is created with OLTP, warehouse, or archive compression, then best compression ratios are achieved with direct-pathINSERT.

See "Consider Using Table Compression" for more information.

# If your table is a basic compressed table, the data to be inserted will be compressed and you must use direct-path INSERT (data inserted in the traditional path will not be compressed at this time ). If your table is compressed in the oltp, warehouse, and archive modes, the direct-pathINSERT mode has the highest data compression efficiency (such as the oltp mode, data inserted in normal mode will also be compressed, but will not be compressed at the time of insertion)

Index Maintenance with Direct-Path INSERT

Oracle Database performs index maintenance at the end of direct-path INSERT operations on tables (partitioned or nonpartitioned) that have indexes. this index maintenance is already med by the parallel execution servers for parallel direct-pathINSERT or by the single process for serial direct-path INSERT. you can avoid the performance impact of index maintenance by making the index unusable before theINSERT operation and then rebuilding it afterward.

See Also:

"Making an Index Unusable" # load data using a direct path. Index maintenance is performed at the end of the operation. If you use parallel direct path loading, the parallel mode will also be used for index maintenance. If you are using serial direct path loading, the index maintenance is also serialized. To avoid the impact of index maintenance during data loading on performance, we can invalidate all data before loading, and then manually re-create the index after loading the data. Space Considerations with Direct-Path INSERT

Direct-path INSERT requires more space than conventional-path INSERT.

# More disk space is required for direct path insertion than for traditional path insertion

All serial direct-path INSERT operations, as well as parallel direct-pathINSERT into partitioned tables, insert data above the high-water mark of the affected segment. This requires some additional space.

# No matter whether it is serial or parallel direct path insertion, the data block above the high water level line is used directly, which requires more space (compared with traditional path insertion)

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 segment-space management mode, you can modify the values of the NEXT and PCTINCREASE storage parameter andMINIMUMEXTENT 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.

# The direct-path INSERT operation adds an exclusive lock to all partitions in the table or partition table. Therefore, you cannot perform dml operations on the target table during direct-path INSERT. The query is acceptable (not the current session of direct-pathINSERT), but the query returns data before direct-pathINSERT.