直接路徑插入 -- 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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- SQL INSERT批次插入方式SQL
- insert批量插入優化方案優化
- [20180319]直接路徑讀特例12c.txt
- 【廖雪峰python入門筆記】list新增元素_append()和insert()Python筆記APP
- [LeetCode] 57. Insert Interval 插入區間LeetCode
- Oracle批量插入資料insert all into用法Oracle
- 產生直接路徑讀的N種情況
- [20180316]共享服務模式和直接路徑讀.txt模式
- 直接插入排序排序
- oracle-資料庫- insert 插入語句Oracle資料庫
- MySQL防止重複插入相同記錄 insert if not existsMySql
- 通過append hint來插入資料，演示它和普通插入資料的效能比較。APP
- 二分搜尋樹系列之[ 插入操作 (insert) ]
- 二分搜尋樹系列之「 插入操作 (insert) 」
- Oracle insert all一次插入多個表中Oracle
- SQL INSERT INTO 語句詳解：插入新記錄、多行插入和自增欄位SQL
- 三種插入排序 直接插入排序，折半插入排序，希爾排序排序
- 直接插入排序演算法排序演算法
- Windows 直接訪問 WSL2 路徑並直接進行讀寫操作，許可權不足解決方法Windows
- PostgreSQL 原始碼解讀（4）- 插入資料#3（heap_insert）SQL原始碼
- 排序演算法——直接插入排序排序演算法
- 排序演算法__直接插入排序排序演算法
- 排序演算法:直接插入排序排序演算法
- Overleaf中插入pdf圖片只顯示圖片路徑的解決方式
- python append（）PythonAPP
- URLSearchParams append()APP
- PostgreSQL 原始碼解讀（102）- 分割槽表#8（資料查詢路由#5-構建APPEND訪問路徑）SQL原始碼路由APP
- Linux檔案的路徑定位-相對路徑和絕對路徑Linux
- canvas 路徑與子路徑Canvas
- HTML絕對路徑與相對路徑HTML
- 直接插入演算法的學習記錄演算法
- Typescript騷操作,在TS裡面直接插入HTMLTypeScriptHTML
- URLSearchParams append() 方法APP
- UNC路徑
- FileUpload路徑
- Oracle中的insert/insert all/insert firstOracle
- nodejs路徑處理方法和絕對路徑NodeJS
- 演算法與資料結構系列 ( 四 ) - 插入排序法- Insert Sort演算法資料結構排序
- 【排序演算法動畫解】直接插入排序排序演算法動畫