Oracle- insert性能优化

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

向表中插入数据有很多办法,但是方法不同，性能差别很大.

----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

转载于:https://blog.51cto.com/cuug520/1532055