Conventional-path insert (traditional path insert), sys. path. insert

Conventional-path insert (traditional path insert), sys. path. insert

We have already mentioned Direct-path.INSERTNow let's talk about Conventional.INSERT. Source: "Oracle Database Administrator's Guide11GRelease 2 (11.2 )"

Conventional and Direct-Path INSERT

You can useINSERTStatement to insert data into a table, partition, or view in two ways: conventionalINSERTAnd direct-pathINSERT. When you issue a conventionalINSERTStatement, 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-pathINSERTIs 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.

• ConventionalINSERTAlways 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-pathINSERTGenerates 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-pathINSERTAlways bypasses undo generation for data changes. # direct path insertion does not generate undo (because undo rollback is not required)

  • If the database is not inARCHIVELOGOrFORCE LOGGINGMode, 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 inARCHIVELOGMode (but not inFORCE LOGGINGMode), then direct-pathINSERTGenerates data redoLOGGINGTables but notNOLOGGINGTables. # 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 inARCHIVELOGAndFORCE LOGGINGMode, then direct-path SQL generate data redo for bothLOGGINGAndNOLOGGINGTables. # 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-pathINSERTIs subject to a number of restrictions. If any of these restrictions is violated, then Oracle Database executes conventionalINSERTSerially 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-pathINSERTStatements 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 thingINSERTAnd DML operations, in a direct-pathINSERTOther 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-pathINSERTStatement, 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-pathINSERTIn 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-pathINSERTIs 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-pathINSERTInto 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 specifyAPPENDOrAPPEND_VALUESHint. However, direct-pathINSERTOperations 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-pathINSERTWhen 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-pathINSERTStatement cannot be or become distributed. # contains direct-pathINSERTObjects 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-12838Error is generated. You must first issueCOMMITStatement 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-pathINSERTOperations, conventionalINSERTOperations do not require an exclusive lock on the table.

Several other restrictions apply to direct-pathINSERTOperations that do not apply to conventionalINSERTOperations. SeeOracle Database SQL Language Reference for information about these restrictions. # about direct-pathINSERT restrictions

You can perform a conventionalINSERTOperation in serial mode or in parallel mode usingNOAPPENDHint.

# You can use the NOAPPEND prompt to activate conventional in serial or parallel modeINSERT

The following is an example of usingNOAPPENDHint to perform a conventionalINSERTIn serial mode:

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

The following is an example of usingNOAPPENDHint to perform a conventionalINSERTIn 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

  • SpecifyPARALLELHint for each insert operation

  • Set the database initialization parameterPARALLEL_DEGREE_POLICYToAUTO

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 ')