Conventional-path insert (traditional path insert), sys. path. insert
We have already mentioned Direct-path.INSERT
Now let's talk about Conventional.INSERT. Source: "Oracle Database Administrator's Guide11GRelease 2 (11.2 )"
Conventional and Direct-Path INSERT
You can useINSERT
Statement to insert data into a table, partition, or view in two ways: conventionalINSERT
And direct-pathINSERT
. When you issue a conventionalINSERT
Statement, Oracle Database reuses free space in the table into which you are inserting and maintains referential integrity constraints. With direct-pathINSERT
, 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 existing data is not reused. this alternative enhances performance during insert operations and is similar to the functionality of the Oracle direct-path loader utility, SQL * Loader. when you insert into a table that has been created in parallel mode, direct-pathINSERT
Is the default.
# Insert data to a table, partition, or view. You can use the traditional path insertion or direct path insertion methods.
When the traditional path insertion method is used, the database will use the free space in the target table (the system will scan below the high water level line during insertion, and use it if there is free space, and expand if there is no free space ), the integrity constraints of the reference are maintained during the Insert Process.
When using direct path insertion, use blocks above the high water level line. Data bypassing the buffer cache is directly written into the data file. The space in the target table is not used. Direct-pathThe INSERT function is similar to the direct-path loader unit SQL * Loader, which can improve the performance of insert operations.
When you insert data into a parallel table, direct-path is used by default.INSERT method.
The manner in which the database generates redo and undo data depends in part on whether you are using conventional or direct-pathINSERT
:
# How database logs are generated depends to some extent on whether you use a traditional path or a direct path to insert logs.
ConventionalINSERT
Always generates maximal redo and undo for changes to both data and metadata, regardless of the logging setting of the table and the archivelog and force logging settings of the database. # whether or not the table logging mode is set, whether the database enables force logging, and whether the database enables archiving, the traditional path insertion method will always generate a large number of redo and undo changes to the metadata of the data.
Direct-pathINSERT
Generates both redo and undo for metadata changes, because these are needed for operation recovery. for data changes, undo and redo are generated as follows: # direct path insertion will generate redo and undo For metadata changes, because these are the information required For recovery. The redo and undo generated for data changes are determined based on the following conditions:
Direct-pathINSERT
Always bypasses undo generation for data changes. # direct path insertion does not generate undo (because undo rollback is not required)
If the database is not inARCHIVELOG
OrFORCE
LOGGING
Mode, then no redo is generated for data changes, regardless of the logging setting of the table. # If the database is not set to archive mode or force logging mode, logs are not generated for data changes unless the logging mode is set for the target table.
If the database is inARCHIVELOG
Mode (but not inFORCE
LOGGING
Mode), then direct-pathINSERT
Generates data redoLOGGING
Tables but notNOLOGGING
Tables. # if it is set to archive mode, but it is not set to force logging, direct path insertion will generate logs for data changes in the table with logging specified, if logging is not specified for a table, logs are not generated.
If the database is inARCHIVELOG
AndFORCE
LOGGING
Mode, then direct-path SQL generate data redo for bothLOGGING
AndNOLOGGING
Tables. # If the database is in archive mode and force logging is set, direct path insertion will generate logs for data changes regardless of whether the table has the logging attribute specified.
Direct-pathINSERT
Is subject to a number of restrictions. If any of these restrictions is violated, then Oracle Database executes conventionalINSERT
Serially without returning any message, unless otherwise noted:
# Direct-pathINSERT has the following restrictions. If any of the following information is met, the database automatically inserts data using a serial traditional path without any feedback.
You can have multiple direct-pathINSERT
Statements in a single transaction, with or without other DML statements. however, after one DML statement alters a participant table, partition, or index, no other DML statement in the transaction can access that table, partition, or index. # if multiple direct-paths exist in the same thingINSERT
And DML operations, in a direct-pathINSERT
Other queries or dml operations after execution (before submission) cannot be performed, and a ORA-12838 is reported during execution
Queries that access the same table, partition, or index are allowed before the direct-pathINSERT
Statement, but not after it. # the same meaning as above
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. # same as above
The target table cannot be of a cluster. # if The target table to be inserted is a cluster table, you cannot use a direct path. You can only use a traditional path.
The target table cannot contain object type columns. # The target table cannot contain columns of The object type.
Direct-pathINSERT
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. # For non-partitioned index organization tables, index organization tables containing mapping tables, or indexed organization tables referenced by views, direct paths cannot be used for loading.
Direct-pathINSERT
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 specifyAPPEND
OrAPPEND_VALUES
Hint. However, direct-pathINSERT
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. # when using Direct-pathINSERT
When inserting data into a single partition or an index organization table with only one partition, even if the index organization table is created in parallel mode, data is inserted in serial mode only. However, you only need to use Direct-pathWhen INSERT inserts data into the index organization table, we do not specify a specific partition extension (such as insert into t_part partition (P1) values (1 ,'***', '***), and the index organization table has multiple partitions, we can use the parallel mode.
The target table cannot have any triggers or referential integrity constraints defined on it. # The target table cannot contain triggers and The referenced integrity constraints.
The target table cannot be replicated. # The target table cannot be copied (to be honest, I don't understand what this article means !!!)
A transaction containing a direct-pathINSERT
Statement cannot be or become distributed. # contains direct-pathINSERT
Objects cannot be distributed.
You cannot query or modify direct-path inserted data immediately after the insert is complete. If you attempt to do so,ORA-12838
Error is generated. You must first issueCOMMIT
Statement before attempting to read or modify the newly-inserted data.
# In the same thing, if the direct-path inserted execution is complete, you cannot perform query and dml operations on the target table before submitting, otherwise the ORA-12838 error will be reported
See Also:
Oracle Database Administrator's Guide for a more complete description of direct-pathINSERT
Oracle Database Utilities for information on SQL * Loader
Oracle Database Performance Tuning Guide for information on how to tune parallel direct-pathINSERT
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-pathINSERT
Operations, conventionalINSERT
Operations do not require an exclusive lock on the table.
Several other restrictions apply to direct-pathINSERT
Operations that do not apply to conventionalINSERT
Operations. SeeOracle Database SQL Language Reference for information about these restrictions. # about direct-pathINSERT restrictions
You can perform a conventionalINSERT
Operation in serial mode or in parallel mode usingNOAPPEND
Hint.
# You can use the NOAPPEND prompt to activate conventional in serial or parallel modeINSERT
The following is an example of usingNOAPPEND
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 usingNOAPPEND
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
SpecifyPARALLEL
Hint for each insert operation
Set the database initialization parameterPARALLEL_DEGREE_POLICY
ToAUTO
Insert statement insertion
The null value is indicated ''.
Insert into table name values ('value1', 'value2', 'value3', 'value4', 'value5 ','','')
You can also use null. After the database is inserted, null is displayed. If you use '', nothing is displayed.
How to Use insert
INSERT statement
Description
This statement can be used to insert a single row into a table (Syntax 1) or insert the results of a SELECT statement into a table (syntax 2 ).
Syntax 1
INSERT [INTO] [owner.] table-name [(column-name,...)]
VALUES (expression ,...)
Syntax 2
INSERT [INTO] [owner.] table-name [(column-name,...)]
SELECT statement
Usage
The INSERT statement is used to add a new row to a database table.
Insert a single row containing the specified expression value. If an optional column name list is provided, values are inserted to the specified column one by one. If the list of column names is not specified, the values are inserted to the table columns in the creation sequence (the same as the order retrieved using SELECT. Insert rows to any position in the table.
If a column name is specified, the columns in the selected list match the specified columns in the column list in sequence or match the columns in the creation order.
Strings inserted into the table are always saved in the case when they are input, regardless of whether the database is case sensitive. Therefore, when the string Value inserted into the table is saved in the database, the Value is always in upper case, and the other letters are in lower case. The string returned by the SELECT statement is Value. However, if the database is case-insensitive, the Value and value are considered the same for all comparisons. In addition, if the primary key of a single column already contains a Value entry, the value will be inserted, because it will cause the primary key to be unique.
The UltraLite table has no owner. The optional owner is supported to facilitate the use of existing SQL and SQL generated programmatically. UltraLite accepts the owner, but ignores it.
Example
Add the Eastern Sales department to the database.
INSERT
INTO department (dept_id, dept_name)
VALUES (230, 'Eastern sales ')