insert /*+ append */直接路徑插入

13811135929發表於2017-03-29
APP


相比於傳統的插入方式，append hints進行insert叫做直接路徑載入插入。其具體的語法如下：

insert /*+ append */
【優缺點】

1. append方式插入資料效率較高，尤其對於大資料量的載入，append效率提高明顯。

2. append方式會直接將新資料插入到表的高水位線以上，沒有充分利用現有空閒空間，因此會造成一定空間上的浪費，尤其對於經常delete的表空間浪費顯得更為突出。

3. 相比常規插入，資料庫在no force_logging模式下append會產生少量的undo資料。

4. insert /*+ append */ into table_name select語句若未commit提交，會造成同一會話無法查詢表。

5. append+nologging方式會產生更少的redo

6. append方式會忽略參照完整性約束
 對於以上觀點如有不妥，還請指正。
【實驗】

  • insert append未提交，同一會話無法查詢情況

點選(此處)摺疊或開啟

  1. create table t(id number);
  2. insert into t select 1 from dual;
  3. SELECT * FROM t;
  4.         ID
  5. ----------
  6.          1

  8. insert /*+ append */ into t select 1 from dual;
  9. SELECT * FROM t;
  10. SELECT * FROM t
  11.               *
  12. ERROR at line 1:
  13. ORA-12838: cannot read/modify an object after modifying it in parallel

可以看到insert /*+ append */ into t select 1 from dual命令沒有commit，同一會話查詢t表會報錯。

  • undo使用情況

點選(此處)摺疊或開啟

  1. SELECT force_logging FROM v$database;
    FORCE_LOGGING
    ------------------------------
    NO
  2. create table t1 as select * from dba_objects;
  3. create table t2 as select * from dba_objects where 1=0;
  4. create table t3 as select * from dba_objects where 1=0;
  1. //常規方式插入
  2. insert into t2 select * from t1;
  3. SELECT sq.SQL_TEXT,t.USED_UBLK FROM v$transaction t,v$session se,v$sql sq where se.TADDR=t.ADDR and se.sql_id=sq.sql_id;
  4. SQL_TEXT                         USED_UBLK
  5. -------------------------------  ----------
  6. insert into t2 select * from t1  29
  7. //直接路徑插入
  8. insert /*+ append */ into t3 select * from t1;
  9. SELECT sq.SQL_TEXT,t.USED_UBLK FROM v$transaction t,v$session se,v$sql sq where se.TADDR=t.ADDR and se.sql_id=sq.sql_id;
  10. SQL_TEXT                                                      USED_UBLK
  11. ------------------------------------------------------------ ----------
  12. insert /*+ append */ into t3 select * from t1                 1
插入相同資料，常規插入一共產生了29個undo block;而直接路徑插入僅僅產生了1個undo block，按照tom權威說法：直接路徑不會為表資料的修改生成UNDO資訊，其undo block主要為資料字典更改或維護索引產生。

  • redo產生情況

歸檔模式下：

點選(此處)摺疊或開啟

  1. create table test1 as select * from dba_objects;
  2. create table test2 as select * from dba_objects where 1=0;
  3. create table test3 as select * from dba_objects where 1=0;
在SID=50會話建立三張表，然後分別常規方式、直接路徑插入方式向test2,test3插入資料。
常規方式插入：
插入前關於SID=50的redo size資料：

點選(此處)摺疊或開啟

  1. select name, value from v$sesstat s, v$statname n where n.statistic# = s.statistic# and upper(name) like upper('%redo size%') and s.sid=50;
  2. NAME                                     VALUE
  3. ----------------------------------- ----------
  4. redo size                                41014992
執行insert into test2 select * from test1後，redo size資料：

點選(此處)摺疊或開啟

  1. select name, value from v$sesstat s, v$statname n where n.statistic# = s.statistic# and upper(name) like upper('%redo size%') and s.sid=50;
  2. NAME                                  VALUE
  3. ----------------------------------- ----------
  4. redo size                            47743616
執行常規插入命令一共生成了(47743616-41014992)/1024/1024=6.4M的redo資訊。按照相同方法，進行直接路徑插入方式生成redo大小同樣為(54498668-47743784)/1024/1024=6.4M。
下面對錶t2,t3設定為nologging

點選(此處)摺疊或開啟

  1. alter table test2 nologging;
  2. alter table test3 nologging;
再進行測試：
insert into test2 select * from test1                                     6.3M
insert /*+ append */ into test3 select * from test1;               8k
直接路徑插入生成的redo大大較少，僅為8k

非歸檔模式：
logging：
insert into test2 select * from test1                                   6.4M
insert /*+ append */ into test3 select * from test1;             15k
nologging：
insert into test2 select * from test1                                   6.38M
insert /*+ append */ into test3 select * from test1;             13.8k

對歸檔/非歸檔模式，nologging/loggingce列個表格進行對比：

 

非歸檔+表logging

非歸檔+表nologging

歸檔+表logging

歸檔+表nologging

常規插入

6.4M

 6.38M

 6.4M

 6.4M

直徑路徑插入

15k

13.8k

 6.4M

8k


在非歸檔模式下，insert /*+ append */無論表是否設定為nologging，相比常規插入都會生成極小的redo；歸檔模式下，結合nologging才會較少redo的產生。

【總結】
縱然insert /*+ append */在某些情況下能夠減少undo的使用，會生成極少的redo資料，但此方式也會帶來一些問題，請在生產環境下結合具體的情況來決定採用直接路徑方式插入還是常規插入。以便儘量發揮各自的優點，規避相應的缺點。


來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29827284/viewspace-2136293/，如需轉載，請註明出處，否則將追究法律責任。

相關文章