Conventional-path insert(傳統路徑插入)，sys.path.insert

Conventional-path insert(傳統路徑插入)，sys.path.insert

   前面我們已經說過了Direct-path INSERT，現在來說一下ConventionalINSERT。文章來源"Oracle Database Administrator's Guide11g Release 2 (11.2)”

Conventional and Direct-Path INSERT

You can use the INSERT 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.

##向表，分區或者視圖中插入資料，我們可以使用傳統路徑插入和直接路徑插入兩種方式。

當使用傳統路徑插入方式時，資料庫會利用目標表中空閑空間(插入時會掃描高水位線以下，如果有空閑空間就利用，如果沒有空閑空間就擴充)，並且在插入過程中會維護引用的完整性條件約束。

當使用直接路徑插入時，使用高水位線之上的塊。資料繞過buffer cache被直接寫入資料檔案。目標表中空間空間不被使用。direct-pathINSERT的功能同direct-path loader單元SQL*Loader相似，可以提高insert操作的效能。

當你向一個並行表中插入資料時，預設使用direct-pathINSERT方式。

 

The manner in which the database generates redo and undo data depends in part on whether you are using conventional or direct-pathINSERT:

##資料庫日誌產生的方式一定程度上取決於你是使用傳統路徑還是直接路徑插入

• Conventional INSERT 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.##不論表是否設定了logging模式，資料庫是否啟用了force logging，資料庫是否啟用了歸檔，傳統路徑插入方式總是會為資料好中繼資料的變化產生大量的redo和undo

• Direct-path INSERT 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:##直接路徑插入會為中繼資料的改變而產生redo和undo，因為這些是進行恢複所需要的資訊。對於資料的變化，其所產生的redo和undo根據下面的條件來決定：

•  

  • Direct-path INSERT always bypasses undo generation for data changes.##直接路徑插入不會產生undo(因為不需要靠undo來復原)

  • If the database is not in ARCHIVELOG or FORCE LOGGING mode, then no redo is generated for data changes, regardless of the logging setting of the table. ##如果資料庫沒有被設定成歸檔模式，也沒有被設定成force logging模式，那麼不會為資料的變化產生日誌，除非目標表設定了logging模式

  • If the database is in ARCHIVELOG mode (but not in FORCE LOGGING mode), then direct-path INSERT generates data redo forLOGGING tables but not forNOLOGGING tables.##如果被設定為歸檔模式，但是沒有被設定我force logging，那麼直接路徑插入會為指定了logging的表的資料變化產生日誌，如果表沒有指定logging那麼就不產生日誌

  • If the database is in ARCHIVELOG and FORCE LOGGING mode, then direct-path SQL generate data redo for bothLOGGING andNOLOGGING tables.##如果資料庫處于歸檔模式，並且設定了force logging，那麼不論表是否指定了logging屬性，直接路徑插入都會為資料變化產生日誌

Direct-path INSERT 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-path INSERT有如下一些限制。如果符合下面任何一條，那麼資料庫會在不給任何反饋資訊的情況下自動的採用串列傳統路徑插入

• You can have multiple direct-path INSERT statements in a single transaction, with or without other DML statements. However, after one DML statement alters a particular table, partition, or index, no other DML statement in the transaction can access that table, partition, or index.##在同一個事物中如果有多個direct-path INSERT和DML操作，在一個direct-pathINSERT執行後(提交前)其他的查詢或者dml操作都無法執行，執行時會報ORA-12838

• Queries that access the same table, partition, or index are allowed before the direct-pathINSERT statement, but not after it.##同上面說的是同一個意思

• 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.##同上

• The target table cannot be of a cluster.##如果要插入的目標表是簇表的話，那麼無法使用直接路徑，只能使用傳統路徑

• The target table cannot contain object type columns.##目標表不能包含物件類型的列

• Direct-path INSERT 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.##對於非分區的索引組織表或者是含有mapping table的索引組織表，或者是被視圖引用的索引組織表是無法使用直接路徑載入的。

• Direct-path INSERT 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 specify theAPPEND orAPPEND_VALUES hint. However, direct-path INSERT 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.##當使用Direct-pathINSERT向單分區或者只有一個分區的索引組織表中插入時，即使索引組織表是使用並行模式建的，也只會使用串列方式插入。但是，只要在使用Direct-pathINSERT向索引組織表中插入資料時，我們沒有指定具體的分區副檔名(如 insert into t_part partition(P1) values(1,'***','***'))，並且索引組織表有多個分區，我們就可以使用並行模式

• The target table cannot have any triggers or referential integrity constraints defined on it.##目標表上不能含有觸發器，以及被引用的完整性條件約束

• The target table cannot be replicated.##目標表不能被複製(說實話沒理解這一條說的是什麼意思！！！)

• A transaction containing a direct-path INSERT statement cannot be or become distributed.##包含direct-pathINSERT的事物不能被分布式

You cannot query or modify direct-path inserted data immediately after the insert is complete. If you attempt to do so, anORA-12838 error is generated. You must first issue aCOMMIT statement before attempting to read or modify the newly-inserted data.

##在同一個事物中如果direct-path inserted執行完成後，提交之前，你不能對目標表進行query以及dml等操作，否則會報ORA-12838錯誤

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-path INSERT operations, conventional INSERT operations do not require an exclusive lock on the table.

Several other restrictions apply to direct-path INSERT operations that do not apply to conventionalINSERT operations. SeeOracle Database SQL Language Reference for information about these restrictions.##關於direct-pathINSERT的使用限制上面已經說了

You can perform a conventional INSERT operation in serial mode or in parallel mode using theNOAPPEND hint.

##你可以使用NOAPPEND提示來啟用串列或者並行模式的conventional INSERT

The following is an example of using the NOAPPEND 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 using the NOAPPEND 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

  • Specify the PARALLEL hint for each insert operation

  • Set the database initialization parameter PARALLEL_DEGREE_POLICY toAUTO

insert語句插入的問題

空值用''表示
insert into 表名 values('value1','value2','value3','value4','value5','','')
用null也可以，插入資料庫後會顯示null，用''的話就什麼都不顯示
 
insert插入怎使用

INSERT 語句

說明
使用此語句可將單行插入到表中（文法 1），或者將 SELECT 語句的結果插入到表中（文法 2）。

文法 1
INSERT [ INTO ] [ owner.]table-name [ ( column-name, ... ) ]
VALUES ( expression, ... )

文法 2
INSERT [ INTO ] [ owner.]table-name [ ( column-name, ... ) ]
SELECT statement

用法
INSERT 語句用於將新行添加到資料庫表中。

插入包含指定的運算式值的單行。如果給出可選的列名列表，則值逐個插入到指定的列中。如果沒有指定列名的列表，則值以建立時的順序（與用 SELECT * 檢索的順序相同）插入到表列中。行插入到表中的任意位置。

如果指定列名稱，挑選清單中的列將按順序與列列表中指定的列匹配，或者按這些列的建立順序匹配。

插入到表中的字串始終以它們輸入時的大小寫儲存，不論資料庫是否區分大小寫。因此，插入到表中的字串 Value 在資料庫中儲存時 V 始終為大寫，其餘的字母為小寫。SELECT 語句返回的字串為 Value。但是，如果資料庫不區分大小寫，所有比較都會將 Value 與 value、VALUE 等視為相同。而且，如果單列主鍵已經包含 Value 條目，則會拒絕插入 value，因為它會導致主鍵不唯一。

UltraLite 表沒有所有者。對可選的 owner 的支援是為了方便使用現有的 SQL 和以編程方式產生的 SQL。UltraLite 接受所有者，但將其忽略。

樣本
將 Eastern Sales 部門添加到資料庫。

INSERT
INTO department ( dept_id, dept_name )
VALUES ( 230, 'Eastern Sales' )