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

相比于传统的插入方式，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
   
7. 
   
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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29827284/viewspace-2136293/