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