2013-07-13 15:59:41

《收获,不止oracle》单车到飞船

各种插入数据方法性能比较,来自《收获,不止oracle》,嘿嘿。。

1.单车速度:

create  or replace procedure hr_pr1
as
begin
for i in 1 .. 100000
  loop
   execute immediate
   'insert into test values('||i||')';
   commit;
  end loop;
end;
HR >set autotrace on;
HR >exec hr_pr1;
PL/SQL proceduresuccessfully completed.
Elapsed: 00:01:21.15
select sql_text,sql_id,parse_calls,executions from v$sql where sql_text like 'insert into test%';
SQL_TEXT                                |SQL_ID       |PARSE_CALLS|EXECUTIONS
----------------------------------------|-------------|-----------|----------
insert into test values(99702)         |b0ttv015q3w7q|          1|         1
insert into test values(99853)         |2qnnf3v613wt2|          1|         1
insert into test values(99419)         |0rfhhq02qmx4c|          1|         1
insert into test values(99554)         |9n5d6v436zx67|          1|         1
insert into test values(99462)         |8k7cs7hr2gxj6|          1|         1
insert into test values(99971)         |fwjvq8wbx3yfc|          1|         1
insert into test values(99569)         |a33nkuvvdvyhk|          1|         1
insert into test values(99486)         |d4j5839cymykz|          1|         1
insert into test values(99679)         |28mwx2pky3yzr|          1|         1
insert into test values(99917)         |9kctuk2nuvz75|          1|         1
insert into test values(99827)         |7uwr4n59dvzhu|          1|         1
589 rowsselected.

2.摩托速度:

create  or replace procedure  hr_pr2
for i in 1 .. 100000
loop
execute immediate
'insert into testvalues(:x)' using i ;
commit;
end loop;
end;
HR >exec hr_pr2;
PL/SQL proceduresuccessfully completed.
Elapsed: 00:00:16.74
SYS >select sql_text,sql_id,parse_calls,executions from v$sql where sql_text like 'insert into test%';
SQL_TEXT                                |SQL_ID       |PARSE_CALLS|EXECUTIONS
----------------------------------------|-------------|-----------|----------
insert into test values(:x)             |dd2q33c7uw2bb|          1|    100000

3.汽车速度:

create  or replace procedure hr_pr3
as
begin
for i in 1 .. 100000
loop
insert into test values(i);
commit;
end loop;
end;
HR >exec hr_pr3;
PL/SQL procedure successfully completed.
Elapsed: 00:00:15.00

4.动车速度:

create  or replace procedure hr_pr4
as
begin
for i in 1 .. 100000
loop
insert into test values(i);
end loop;
commit;
end;
HR >exec hr_pr4;
PL/SQL procedure successfully completed.
Elapsed: 00:00:10.22

5.飞机速度:

HR >insert into test select rownum from  dual  connect by level<=100000;
100000 rows created.
Elapsed: 00:00:02.09

6.火箭速度:

HR >create table test as select rownum x from dual connect by level<=100000;
Tablecreated.
Elapsed: 00:00:01.05

7.飞船速度:

HR >create table test  nologging parallel 2 as  select rownum x from dual connect by level<=100000; 
Tablecreated.
Elapsed: 00:00:00.55
发表回复