oracle- insert性能优化

engins

看见朋友导入数据，花了很长时间都没完成！其实有很多快速的方法，整理下！

• [Shell] 纯文本查看 复制代码

  向表中插入数据有很多办法,但是方法不同，性能差别很大. 
   
   
  ----1.原始语句 
  drop table t1 purge; 
   
  create table t1 
  ( 
  sid number, 
  sname varchar2(20) 
  ) tablespace test; 
   
   
   
  create or replace procedure proc01 
  ( 
  sname varchar2 
  ) 
  as 
  begin 
      for i in 1..10000000 
      loop 
          execute immediate 
          'insert into t1(sid,sname) values('||i||','''||sname||''')'; 
      commit; 
      end loop; 
  end; 
  / 
   
   
   
  alter system flush shared_pool; 
   
  set timing on; 
   
  exec proc01('ocpyangtest'); 
   
  已用时间:  02: 02: 54.12 
   
   
   
   
   
  ----2.绑定变量 
   
   
   
  drop table t1 purge; 
   
  create table t1 
  ( 
  sid number, 
  sname varchar2(20) 
  ) tablespace test; 
   
   
  create or replace procedure proc02 
  ( 
  sname varchar2 
  ) 
  as 
  begin 
      for i in 1..10000000 
      loop 
          execute immediate 
      'insert into t1(sid,sname) values(:no'||','''||sname||''')' using i; 
      commit; 
      end loop; 
  end; 
  / 
   
   
   
  alter system flush shared_pool; 
   
  set timing on; 
   
  exec proc02('ocpyangtest'); 
   
  已用时间:  00: 22: 59.79 
   
   
  select count(*) from t1; 
   
   
  ----3.静态语句 
   
   
   
  drop table t1 purge; 
   
  create table t1 
  ( 
  sid number, 
  sname varchar2(20) 
  ) tablespace test; 
   
   
   
   
  create or replace procedure proc03 
  as 
  begin 
      for i in 1..10000000 
      loop 
           
      insert into t1 values(i,'ocpyangtest'); 
      commit; 
      end loop; 
  end; 
  / 
   
  alter system flush shared_pool; 
   
  set timing on; 
   
  exec proc03; 
   
  已用时间:  00: 20: 42.42 
   
  select count(*) from t1; 
   
   
   
   
   
   
  ----4.批量提交 
   
   
  drop table t1 purge; 
   
  create table t1 
  ( 
  sid number, 
  sname varchar2(20) 
  ) tablespace test; 
   
   
   
   
  create or replace procedure proc04 
  as 
  begin 
      for i in 1..10000000 
      loop         
      insert into t1 values(i,'ocpyangtest'); 
      end loop; 
      commit; 
  end; 
  / 
   
   
  alter system flush shared_pool; 
   
  set timing on; 
   
  exec proc04; 
   
  已用时间:  00: 11: 48.42 
   
   
   
  ----5.集合 
   
   
  drop table t2 purge; 
   
  create table t2 
  ( 
  sid number, 
  sname varchar2(20) 
  ) tablespace test; 
   
   
   
   
  alter system flush shared_pool; 
   
  select count(*) from t1; 
   
   
  set timing on; 
   
   
   
  insert into t2 select sid,sname from t1; 
   
   
  已用时间:  00: 01: 02.18 
   
  commit; 
   
   
  select count(*) from t2; 
   
   
   
  ----6. 集合+append 
   
   
  drop table t2 purge; 
   
  create table t2 
  ( 
  sid number, 
  sname varchar2(20) 
  ) tablespace test; 
   
   
   
   
  alter system flush shared_pool; 
   
   
  select count(*) from t1; 
   
   
   
  set timing on; 
   
   
   
  insert /*  + append */ into t2 select sid,sname from t1; 
   
  已用时间:  00: 00: 36.94 
   
   
  commit; 
   
   
  select count(*) from t2; 
   
   
   
   
   
   
  ----7. 集合+append+nologging 
   
   
  drop table t2 purge; 
   
  create table t2  
  ( 
  sid number, 
  sname varchar2(20) 
  ) nologging tablespace test; 
   
   
   
   
  alter system flush shared_pool; 
   
   
  select count(*) from t1; 
   
   
   
   
   
   
  set timing on; 
   
   
  insert /*  + append */ into t2 select sid ,sname from t1; 
   
  已用时间:  00: 00: 35.07 
   
  commit; 
   
   
  select count(*) from t2; 
   
   
   
   
   
    
  ----8.数据加载1 
   
   
  drop table t2 purge; 
   
   
   
  alter system flush shared_pool; 
   
  select count(*) from t1; 
   
   
   
  set timing on; 
   
   
   
  create table t2 
  as 
  select sid,sname from t1; 
   
  已用时间:  00: 00: 25.91 
   
   
   
  select count(*) from t2; 
   
   
   
   
   
   
  ----9.数据加载2 
   
   
  drop table t2 purge; 
   
   
   
   
  alter system flush shared_pool; 
   
  select count(*) from t1; 
   
   
   
  set timing on; 
   
   
   
  create table t2 nologging 
  as 
  select sid,sname from t1; 
   
  已用时间:  00: 00: 04.89 
   
   
   
  select count(*) from t2; 
   
   
   
  ----10.数据加载+并行 
   
   
  drop table t2 purge; 
   
   
   
   
  alter system flush shared_pool; 
   
  select count(*) from t1; 
   
   
   
  set timing on; 
   
   
   
  create table t2 nologging parallel 24   --根据自己服务器情况 
  as 
  select sid,sname from t1; 
   
  已用时间:  00: 00: 02.89 
   
   
  select count(*) from t2; 

  
  

jjfjjj

美女未抱身先走,常使色狼泪满襟。。。。。。

wuaji

我的id是假冒的，大家不要相信我是骗子。

cz-sjm

恋爱就是无数个饭局，结婚就是一个饭局。

downmovies

啥时硬件也可以COPY就好了!

qq489498494

站的更高，尿的更远。

q528

你的丑和你的脸没有关系。。。。。。