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未提交，同一会话无法查询情况
点击(此处)折叠或打开
-
create table t(id number);
-
insert into t select 1 from dual;
-
SELECT * FROM t;
-
ID
-
----------
-
1
-
-
insert /*+ append */ into t select 1 from dual;
-
SELECT * FROM t;
-
SELECT * FROM t
-
*
-
ERROR at line 1:
- ORA-12838: cannot read/modify an object after modifying it in parallel
可以看到insert /*+ append */ into t select 1 from dual命令没有commit，同一会话查询t表会报错。
-
undo使用情况
点击(此处)折叠或打开
-
SELECT force_logging FROM v$database;
FORCE_LOGGING
------------------------------
NO -
create table t1 as select * from dba_objects;
-
create table t2 as select * from dba_objects where 1=0;
-
create table t3 as select * from dba_objects where 1=0;
- //常规方式插入
- insert into t2 select * from t1;
- 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;
-
SQL_TEXT USED_UBLK
-
------------------------------- ----------
- insert into t2 select * from t1 29
- //直接路径插入
-
insert /*+ append */ into t3 select * from t1;
- 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;
-
SQL_TEXT USED_UBLK
-
------------------------------------------------------------ ----------
- insert /*+ append */ into t3 select * from t1 1
-
redo产生情况
点击(此处)折叠或打开
-
create table test1 as select * from dba_objects;
-
create table test2 as select * from dba_objects where 1=0;
- create table test3 as select * from dba_objects where 1=0;
常规方式插入：
插入前关于SID=50的redo size数据：
点击(此处)折叠或打开
- 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;
-
NAME VALUE
-
----------------------------------- ----------
- redo size 41014992
点击(此处)折叠或打开
- 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;
-
NAME VALUE
-
----------------------------------- ----------
- redo size 47743616
下面对表t2,t3设置为nologging
点击(此处)折叠或打开
-
alter table test2 nologging;
- 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博客 ” ，链接：https://blog.itpub.net/29827284/viewspace-2136293/ ，如需转载，请注明出处，否则将追究法律责任。
