单车到飞船

来自《收获，不止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