热衷技术，热爱交流
分类： Oracle
2013-07-07 02:51:30
来自《收获，不止oracle》，嘿嘿。。
单车速度：
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 >select name,text from user_errors;
NAME
------------------------------
TEXT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
HR_PR1
PLS-00103: Encountered the symbol "IN" whenexpecting one of the following:
<an identifier> <a double-quoted delimited-identifier>
1 rowselected.
HR >create or replace procedurehr_pr1
2 as
3 begin
4 for i in 1 .. 100000
5 loop
6 execute immediate
7 'insertinto test values('||i||')';
8 commit;
9 end loop;
10 end;
11 /
Procedurecreated.
HR >set autotrace on;
HR >exec hr_pr1;
PL/SQL proceduresuccessfully completed.
Elapsed: 00:01:21.15
selectsql_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.
摩托速度：
create or replace procedure hr_pr2
as
begin
for i in 1 .. 100000
loop
execute immediate
'insert into testvalues(:x)' using i ;
commit;
end loop;
end;
HR>create or replace procedure hr_pr2
2 as
3 begin
4 for i in 1 .. 100000
5 loop
6 execute immediate
7 'insertinto test values(:x)' using i ;
8 commit;
9 end loop;
10 end;
11 /
Procedurecreated.
HR >exec hr_pr2;
PL/SQL proceduresuccessfully completed.
Elapsed: 00:00:16.74
SYS >select sql_text,sql_id,parse_calls,executionsfrom v$sql where sql_text like 'insert into test%';
SQL_TEXT |SQL_ID |PARSE_CALLS|EXECUTIONS
----------------------------------------|-------------|-----------|----------
insert into test values(:x) |dd2q33c7uw2bb| 1| 100000
汽车速度：
create or replace procedure hr_pr3
as
begin
for i in 1 .. 100000
loop
insert into test values(i);
commit;
end loop;
end;
Procedurecreated.
HR >exec hr_pr3;
PL/SQL proceduresuccessfully completed.
Elapsed: 00:00:15.00
动车速度：
create or replace procedure hr_pr4
as
begin
for i in 1 .. 100000
loop
insert into test values(i);
end loop;
commit;
end;
Procedurecreated.
HR >exec hr_pr4;
PL/SQL proceduresuccessfully completed.
Elapsed: 00:00:10.22
飞机速度：
HR >insert into test select rownum from dual connect by level<=100000;
100000 rowscreated.
Elapsed: 00:00:02.09
火箭速度：
HR >create table test as select rownum x from dualconnect by level<=100000;
Tablecreated.
Elapsed: 00:00:01.05
飞船速度：
HR >create table test nologging parallel 2 as select rownum x from dualconnect by level<=100000;
Tablecreated.
Elapsed: 00:00:00.55
