oracle append作用分析

这个hint是针对整个sql,让数据库以直接加载的方式(direct load)将数据加载入库。这个提示不会检查当前是否有插入所需要的块空间，相反它会直接将数据添加到新块中。这样会浪费空间，但可以提高插入的性能。需要注意的是，数据将被存储在HWM之上的位置。
为啥性能好呢?
1.插入的时候不走buffer cache(共享池);
2.不会在高水位线(HWM)下面去找是否存在能插入的数据块;
3.在非归档模式或者归档模式下但是nologing,使用append在插入数据时不会产生redo;
但是执行sql过程会产生少量redo;
副作用:
1.占用的磁盘空间比普通的多;
2.append时会在目标表加lmode=6的排它锁(TM enqueue),此时他人人无法对其DML操作;

在不同版本作用不一样:

append在10g只能运用到 insert into..values select语句;
在11gR2,在insert values也可以支持,为append_values;
Insert并发也是直接加载的方式;

要注意的:
1.是用append/append_values时:必须要先提交:
否则查询报错:
ORA-12838: 无法在并行模式下修改之后读/修改对象;
2.append_values不要在单行insert里面使用,否则造成极大的空间浪费呀;
结合绑定变量批量提交比较合适;
3.insert要使用并发插入,前提: alter session enable parallel dml;
否则不会生效;真正的并发插入是直接加载方式;且必须查看真实的执行计划才可以看到;

1先验证一下,append的并发插入

drop table ysy_t;                   
create table ysy_t 
as select a.OBJECT_ID,a.OBJECT_NAME,a.OBJECT_TYPE  from dba_objects a where 1=2;

drop table ysy_t2;
create table ysy_t2
as
select * from dba_objects;
--未开启并发
insert /*+ parallel*/ into  ysy_t 
select a.OBJECT_ID, a.OBJECT_NAME, a.OBJECT_TYPE
  from ysy_t2  a
 where object_id > 20 and object_id <40; 

注意:此时的插入加载方式还是普通插入CONVENTIONAL;
你看到并发实际上是select的;因为parallel作用的是整个语句;
等价:
insert  into  ysy_t 
select /*+ parallel*/ a.OBJECT_ID, a.OBJECT_NAME, a.OBJECT_TYPE
  from ysy_t2  a
 where object_id > 20 and object_id <40;

然后开启并发插入
alter session enable parallel dml; 
explain  plan for
insert /*+ parallel*/ into  ysy_t 
select a.OBJECT_ID, a.OBJECT_NAME, a.OBJECT_TYPE
  from ysy_t2  a
where object_id > 20 and object_id <40;
select * from table(dbms_xplan.display); 

你会发现,预估的执行计划parallel不起作用,此时并没有真正的并发插入;
这个需要查看真实的执行计划才可以看到:
select * from v$sql aa where aa.SQL_TEXT like ‘%insert /+ parallel/ into%’;
select * from table(dbms_xplan.display_cursor(‘7txkrca00mk63’,0,’advanced’));


可以发现出现了 load as select,说明开启dml并发后,果然走了直接加载的方式;
如果你用set autotrace on 查看执行计划呢?

这个应该也是加锁的原因导致的,但是也恰好说明并发插入确实是以直接加载的方式在执行;
第三点的验证这里就完了;

2在看看append方式插入后,查询会不会产生物理读

--验证
直接路径访问是绕过SGA，直接把数据读入到PGA中，这个过程数据不经过SGA
的缓冲，所以理论上应该是更快。在PGA中的数据只能有当前PGA进程能够访
问，当其他会话需要访问这部分数据时需要从磁盘读取数据，发生磁盘读。
drop table ysy_t;                   
create table ysy_t 
as select a.OBJECT_ID,a.OBJECT_NAME,a.OBJECT_TYPE  from dba_objects a where 1=2;     

drop table ysy_t2;
create table ysy_t2
as
select * from dba_objects;

--普通
insert into  ysy_t 
select a.OBJECT_ID, a.OBJECT_NAME, a.OBJECT_TYPE
  from ysy_t2  a
 where object_id < 20;
插完:查询会直接走缓存;

查一下数据:select * from ysy_t a where a.object_id=15;此时的物理读为0;

--append方式
insert /*+ append*/ into  ysy_t 
select a.OBJECT_ID, a.OBJECT_NAME, a.OBJECT_TYPE
  from ysy_t2  a
 where object_id > 20 and object_id <40;

插完:第一次查询会走磁盘读,后面再查就是缓存了;
select * from ysy_t a where a.object_id=26;

这点验证完毕;

再看看append_values方式插入,每一条数据是不是占用一个块;
浪费的不要不要的

insert /*+append_values*/ into ysy_t
  (OBJECT_ID, OBJECT_NAME, OBJECT_TYPE)
values
  ('90', 'ysy', '05');

insert /*+append_values*/ into ysy_t
  (OBJECT_ID, OBJECT_NAME, OBJECT_TYPE)
values
  ('90', 'ysy', '06');
  好,插入2条记录;先不提交;
直接查询会报错:
  select * from  ysy_t;说明了appand_values跟append一样,必须要先提交下才能查;

查看一下,刚插入的2条记录在那个块上面
select rowid, dbms_rowid.rowid_block_number(rowid)
from ysy_t a
where a.object_name = ‘ysy’;

可以看到,2条数据分别占用了2个块;简直浪费
要知道,一个8k块,最多可以存700多行数据的;

于是强烈建议在批量使用场合使用:

drop table  ysy_t;
create table   ysy_t
as
select * from dba_objects where 1=2;


drop table  ysy_t_append;
create table   ysy_t_append
as
select * from dba_objects where 1=2;

-- append_values
declare
   type  t_type is table of ysy_t_append%rowtype;  
   v_tab t_type;
   cursor c_get_t is 
   select * from dba_objects;
begin
    open c_get_t;
    fetch c_get_t bulk collect into v_tab;
    forall x in 1.. v_tab.count
      insert /*+ append_values*/ into  ysy_t_append values v_tab(x);
    commit;
    close c_get_t;
end;

查看 每个文件号里面每个块存放多少行数据
select fno,blockno,count(1) from (
select dbms_rowid.rowid_relative_fno(rowid) fno,
       dbms_rowid.rowid_block_number(rowid) blockNo
  from ysy_t_append)
  group by fno,blockNo
  order  by 3 desc;
  这个可以自行测试,可以发现,即保证了效率,也不至于太浪费空间;