Oracle-insert性能优化

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了Oracle-insert性能优化，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含3303字，纯文字阅读大概需要5分钟。

内容图文

看见朋友导入数据，花了很长时间都没完成！其实有很多快速的方法，整理下！ 向表中插入数据有很多办法,但是方法不同，性能差别很

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

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

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

内容总结

以上是互联网集市为您收集整理的Oracle-insert性能优化全部内容，希望文章能够帮你解决Oracle-insert性能优化所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端

---