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;