Oracle SQL：减少commit次数以提高sql的性能

详细的分析http://grassbell.itpub.net/post/26/926

Oracle数据库中，当需要批量对数据库执行插入或者更新操作时候，减少commit的次数，从而减少transaction的数量，而在每个transaction中包含合适的数据处理量，可以提高sql的性能。测试sql如下：

SQL>
SQL> set timing on
SQL> set serveroutput on
SQL>
SQL> drop table testex;

Table dropped.

Elapsed: 00:00:00.05
SQL>
SQL> create table testex (
  2  n number,
  3  a varchar2(10),
  4  b varchar2(100),
  5  c number,
  6  d date);

Table created.

Elapsed: 00:00:00.00
SQL>
SQL> begin
  2  for i in 1..200000 loop
  3  insert into testex values(i,'test','testtesttestteset',i,sysdate);
  4  commit;
  5  end loop;
  6  commit;
  7 
  8  end;
  9  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:24.06
SQL>
SQL> delete from testex;

200000 rows deleted.

Elapsed: 00:00:14.09
SQL> commit;

Commit complete.

Elapsed: 00:00:00.00
SQL>
SQL> begin
  2  for i in 1..200000 loop
  3  insert into testex values(i,'test','testtesttestteset',i,sysdate);
  4  end loop;
  5  commit;
  6 
  7  end;
  8  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:17.09
SQL>
SQL> spool off