直接路徑插入 -- insert /*+append*/ into [zt]
http://www.itpub.net/thread-965298-1-1.html
一、直接路徑插入與間接路徑插入的不同
這個問題相信很多人都已經知道了，為了方便初學者，我再來重審一遍。
create table 表1 as select 列1，列2，... select 表2
insert /*+append*/ into 表1 select 列1，列2，... select 表2
如上形式的插入，都叫做直接路徑插入。當然，在SQL*Loader中也有直接路徑插入的形式。
所謂直接路徑插入，就是繞過Buffer cache，直接將資料插入進表所在資料檔案中。
假如有表AA，要將AA中的資料插入進表BB，在普通的間接插入下，先將AA的資料塊傳進Buffer cache，再將BB的塊也傳進Buffer cache，在Buffer cache中從AA的塊中讀出行，插入進BB的塊中。BB的塊就都變成了髒塊，再等待DBWn把它們寫進資料檔案。因此，間接路徑插入後，AA表的塊和BB表的塊都會在Buffer cache中出現。
而直接路徑插入下，將AA表的資料塊傳進Buffer cache中，讀出行，直接寫進BB表所在的資料檔案。插入完畢後，除了表頭塊外，BB表的資料塊並不會出現在Buffer cache中。
下面來試驗一下：
步1：準備試驗用表：
SQL> create table aa(id number(4),name varchar2(5));
表已建立。
SQL> create table bb(id number(4),name varchar2(5));
表已建立。
SQL> insert into aa values(1,'aa');
已建立 1 行。
SQL> insert into aa values(2,'bb');
已建立 1 行。
SQL> insert into aa values(3,'cc');
已建立 1 行。
SQL> insert into aa values(4,'dd');
已建立 1 行。
SQL> commit;
提交完成。
SQL> select dbms_rowid.rowid_relative_fno(rowid),dbms_rowid.rowid_block_number(rowid) from aa;
DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------------------------ ------------------------------------
4 18493
4 18493
4 18493
4 18493
現在AA表中有4行，佔用塊18493。BB表中沒有資料。
步2：將buffer cache清空，我這裡使用重啟資料庫的方法：
SQL> shutdown immediate
SQL> startup
步3：先用直接路徑插入，從AA表向BB表插入資料：
SQL> insert /*+ append*/ into bb select * from aa;
已建立4行。
SQL> commit;
提交完成。
步4：使用V$bh檢視Buffer cache中的塊：
SQL> select file#,block# from v$bh where objd=(select object_id from user_objects where object_name='AA');
FILE# BLOCK#
---------- ----------
4 18491
4 18491
4 18494
4 18492
4 18495
4 18493 4 18496
已選擇7行。
由於對AA表進行了全表掃描，因此，AA表中高水點下的所有塊都被讀進了Buffer cache，這其中當然包括包含資料的塊18493。
SQL> select file#,block# from v$bh where objd=(select object_id from user_objects where object_name='BB');
FILE# BLOCK#
---------- ----------
4 18499
4 18499
4 18497
SQL> select dbms_rowid.rowid_relative_fno(rowid),dbms_rowid.rowid_block_number(rowid) from bb;
DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------------------------ ------------------------------------
4 18500
4 18500
4 18500
4 18500
上面兩個查詢可以看到，BB表中的資料佔用第18500塊，但是，直接路徑插入後，18500塊並沒被調進Buffer cache。Buffer cache中只有18499和18497。 其中18499是段頭塊，而18497是L1塊，直接路徑插入後，要修改L1塊中的資料塊使用情況。
步5：再試一次間接路徑插入：
SQL> insert into bb select * from aa;
已建立4行。
SQL> commit;
提交完成。
SQL> select file#,block# from v$bh where objd=(select object_id from user_objects where object_name='BB');
FILE# BLOCK#
---------- ----------
4 18504 4 18499
4 18499
4 18502
4 18497
4 18500
4 18503
4 18498
4 18501
已選擇9行。
SQL> select dbms_rowid.rowid_relative_fno(rowid),dbms_rowid.rowid_block_number(rowid) from bb;
DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------------------------ ------------------------------------
4 18500
4 18500
4 18500
4 18500
4 18504
4 18504
4 18504
4 18504
已選擇8行。
從上面的實驗可以證明，間接路徑插入，要先將資料塊傳進Buffer cache。這是Oracle通常修改資料的方式，不對資料檔案直接進行修改，而是在記憶體中完成修改，再由日誌提供保護。對於小量資料的修改，這種方法的效能還是很不錯的。但是大量資料的修改，直接路徑插入將可以提供更好的效能。
直接路徑插入除去少了將BB表的塊傳進Buffer cache這一步外，它還不產生回滾資訊，下面來進一步的實驗：
二、直接路徑插入與回滾：
步1：再次向BB中直接路徑插入：
SQL> insert /*+ append*/ into bb select id+4,name from aa;
步2：檢視事務資訊：
SQL> select xidusn,xidslot,xidsqn,ubafil,ubablk,ubasqn from v$transaction;
XIDUSN XIDSLOT XIDSQN UBAFIL UBABLK UBASQN
---------- ---------- ---------- ---------- ---------- ----------
11 23 854 0 0 0
因為當前只有一個事務，因此選擇 v$transaction 檢視時沒有加條件。從上面的顯示結果可以看到，UBAFIL、UBABLK為0。也就是此事務並沒有對應的回滾塊，只在回滾段頭的事務表中佔用了一行而已。
直接路徑插入是如何提供回滾的呢？觀察BB表高水點的變化，就可以解答這個問題：
步3： 查詢BB表的高水點：
SQL> select header_file,header_block from dba_segments where segment_name='BB';
HEADER_FILE HEADER_BLOCK
----------- ------------
4 18499
SQL> alter system dump datafile 4 block 18499;
系統已更改。
查詢轉儲檔案：
Extent Control Header
-----------------------------------------------------------------
Extent Header:: spare1: 0 spare2: 0 #extents: 2 #blocks: 16
last map 0x00000000 #maps: 0 offset: 2716
Highwater:: 0x01004849（高水點） ext#: 0 blk#: 8 ext size: 8
高水點是4號檔案18505塊。
步4：提交後檢視直接路徑插入到哪個塊中：
SQL> commit;
SQL> select dbms_rowid.rowid_relative_fno(rowid),dbms_rowid.rowid_block_number(rowid) from bb where id>=5;
DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------------------------ ------------------------------------
4 18505
4 18505
4 18505
4 18505
本次插入的資料ID列值為5、6、7、8，透過上面的查詢，本次直接路徑插入，資料被存進18505號塊。而提交前的高水點正是18505。
|--------------|--------|--------|----------
|資料塊 ...... |18503|18504|18505
|--------------|--------|--------|----------
^
|
|
此處是高水點，直接路徑插入從此塊開始分配空間
直接路徑插入，是在高水點之上分配臨時段，將資料插入時進此臨時段中。在提交後將高水點提升至臨時段之上。
現在已經提交，再檢視高水點資訊：
SQL> alter system dump datafile 4 block 18499;
系統已更改。
Extent Control Header
-----------------------------------------------------------------
Extent Header:: spare1: 0 spare2: 0 #extents: 2 #blocks: 16
last map 0x00000000 #maps: 0 offset: 2716
Highwater:: 0x0100484a（剛才是4849） ext#: 1 blk#: 1 ext size: 8
高水點升至18506塊，如下圖：
|--------------|--------|--------|--------|-----
|資料塊 ...... |18503|18504|18505|18506
|--------------|--------|--------|--------|-----
^
|
|
高水點上升至此處
步5：再試一次直接路徑插入回滾時的情況：
SQL> insert /*+ append*/ into bb select id+8,name from aa;
已建立4行。
猜想一下，此次插入應該插入進18506，如果提交的話，就提升高水點到18507，如果回滾的話，保持高水點不變。
檢視高水點，當前仍是18506：
SQL> alter system dump datafile 4 block 18499;
系統已更改。
Extent Control Header
-----------------------------------------------------------------
Extent Header:: spare1: 0 spare2: 0 #extents: 2 #blocks: 16
last map 0x00000000 #maps: 0 offset: 2716
Highwater:: 0x0100484a ext#: 1 blk#: 1 ext size: 8
如果提交，肯定會變為18507，這個我們在步4已經被證明了，現在我們回滾：
SQL> rollback;
回退已完成。
現在已經回滾，檢視高水點資訊：
SQL> alter system dump datafile 4 block 18499;
系統已更改。
Extent Control Header
-----------------------------------------------------------------
Extent Header:: spare1: 0 spare2: 0 #extents: 2 #blocks: 16
last map 0x00000000 #maps: 0 offset: 2716
Highwater:: 0x0100484a ext#: 1 blk#: 1 ext size: 8
仍是18506。
也就是說：
|--------------|--------|--------|--------|--------|------
|資料塊 ...... |18503|18504|18505|18506|18507
|--------------|--------|--------|--------|--------|-----
^
|
|
高水點在此處
資料插入至此處
提交後，高水點升至18507，而如果回滾的話，高水點不變：
|--------------|--------|--------|--------|--------|------
|資料塊 ...... |18503|18504|18505|18506|18507
|--------------|--------|--------|--------|--------|------
^
|
|
回滾後高水點仍在此處
三、直接路徑插入與索引
直接路徑插入時，不產生表塊的回滾資訊，依賴高水點實現回滾。但時，如果表有索引，將會產生索引的回滾資訊，而且索引的塊會被讀時Buffer cache。也就是說，資料不能“直接插入”進索引。下面實驗一下：
步1：為表BB建立一個索引：
SQL> create index bb_id on bb(id);
SQL> select file#,block# from v$bh where objd=(select object_id from user_objects where object_name='BB_ID');
FILE# BLOCK#
---------- ----------
4 18515 4 18513 4 18516 4 18514 重啟資料庫，清空Buffer cache
步2：
SQL> insert /*+ append*/ into bb select * from aa;
已建立4行。
步3：
SQL> select file#,block# from v$bh where objd=(select object_id from user_objects where object_name='BB_ID');
FILE# BLOCK#
---------- ----------
4 18516
直接路徑插入時，索引塊將會被調入Buffer cache。
步4：
SQL> select xidusn,xidslot,xidsqn,ubafil,ubablk,ubasqn from v$transaction;
XIDUSN XIDSLOT XIDSQN UBAFIL UBABLK UBASQN
---------- ---------- ---------- ---------- ---------- ----------
12 9 548 5 2896 255
並且，對於索引塊的修改，將會產生回滾資訊，回滾資訊儲存在回滾塊2896處。
因此，索引並不會“直接路徑插入”，因此，插入的索引資料，應該是在高水點之下：
SQL> select header_file,header_block from dba_segments where segment_name='BB_ID';
HEADER_FILE HEADER_BLOCK
----------- ------------
4 18515
SQL> alter system dump datafile 4 block 18515;
系統已更改。
Extent Control Header
-----------------------------------------------------------------
Extent Header:: spare1: 0 spare2: 0 #extents: 1 #blocks: 8
last map 0x00000000 #maps: 0 offset: 2716
Highwater:: 0x01004855（高水點） ext#: 0 blk#: 4 ext size: 8
高水點在18517處。插入的索引資料在18515處，在高水點之下。
在文件中也曾建議，如果使用直接路徑插入，向表中傳送大量資料，可以先將表上的索引刪掉，插入結束後，再重新建立索引。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/35489/viewspace-1001818/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- insert /*+ append */直接路徑插入APP
- 關於直接路徑插入的工作原理
- oracle常規與直接路徑插入區別Oracle
- 關於insert /*+ append*/ 各種insert插入速度比較APP
- 直接路徑插入模式的一些討論模式
- 【Direct-Path】直接路徑載入提升插入效率及其自身限制
- insert /*+ append */ into 與insert into 的區別APP
- Oracle直接路徑載入Oracle
- insert 中append 用法詳解APP
- append插入不能多次未提交插入資料APP
- nologging和insert /*+append*/APP
- Append與Direct-Path Insert（一）APP
- Append與Direct-Path Insert（二）APP
- nologging與append ztAPP
- APPEND,bulk collect,正常插入比較APP
- 臨時表的APPEND方式插入APP
- insert append需要注意的問題APP
- 如何讓insert /*+ append */ 採用並行。APP並行
- 索引對直接路徑載入的影響索引
- [Oracle] Append hint(insert sql) will lead a TM LOCKOracleAPPSQL
- CTAS和insert append的一個測試APP
- insert /*+ append */於report unrecoverable命令實驗。APP
- 關於insert /* append */的幾點註記APP
- insert批量插入優化方案優化
- [CareerCup] 5.1 Insert Bits 插入位
- 直接insert與儲存過程insert效能區別儲存過程
- 直接插入排序排序
- 【INSERT】在INSERT插入語句中引入條件限制選項實現資料插入控制
- 【SQL】 Multi table insert 多表插入操作SQL
- oracle insert all多表插入的示例Oracle
- Direct Path Insert與APPEND, PARALLEL的梳理與小結APPParallel
- insert語句中append提示對欄位的檢查APP
- 直接插入排序法排序
- Word2013支援直接插入播放網路影片
- MySQL INSERT插入條件判斷：如果不存在則插入MySql
- Oracle批量插入資料insert all into用法Oracle
- c++ insert iterators 插入型迭代器C++
- MySQL：使用INSERT 插入多條記錄MySql