我们来比较一下下列SQL插入大量数据时的速度对比。
动态写法->静态写法->批量插入写法->集合写法
1.
sqlplus scott/tiger
create table t(x int);
--将共享池清空
alter system flush shared_pool;
编写一个简单的存储过程，实现将1到10万的值插入t表的需求。
create or replace procedure proc1
as
begin
for i in 1..100000
loop
execute immediate
'insert into values('||i||')';
commit;
end loop;
end;
/
接下来
drop table t purge;
create table t(x int);
alter system flush shared_pool;
SCOTT@ PROD>set timing on
SCOTT@ PROD>exec proc1;
PL/SQL procedure successfully completed.
Elapsed: 00:00:46.30
SCOTT@ PROD>select count(*) from t;
COUNT(*)
----------
100000
2.利用绑定变量实现再次加速
SCOTT@ PROD>create or replace procedure proc2
2 as
3 begin
4 for i in 1..100000
5 loop
6 execute immediate
7 'insert into t values(:x)'using i;
8 commit;
9 end loop;
10 end;
11 /
接下来
SCOTT@ PROD>drop table t purge;
SCOTT@ PROD>create table t(x int);
SYS@ PROD>alter system flush shared_pool;
SCOTT@ PROD>exec proc2;
PL/SQL procedure successfully completed.
Elapsed: 00:00:10.97
SCOTT@ PROD>select count(*) from t;
COUNT(*)
----------
100000
对比时间加速了不少，这是因为第一种在共享池中每次执行插入语句有很多，而SQL_ID各自不同，每个语句都只是解析1次，执行1次解析了10万次，而第二种加了绑定变量，10万条语句可以被HASH成一个SQL_ID值，解析1次，执行10万次，所以速度也就加快了。
注：细心的人会发想为什么会用到execute immediate,这是一种动态的SQL语句写法，常用于表名字段名是变量，入参的情况，由于表名都不知道，所以当让不能直接写SQL语句了，所以要靠动态SQL语句根据传入的表名参数，来拼成一条SQL语句，由execute immediate调用执行，但是这里显然是多此一举，直接insert into t values(i)完全可以，因为表名就是t.接下来然我们看看直接用后者的情况。
3.
create or replace procedure proc3
as
begin
for i in 1..100000
loop
insert into t values(i);
commit;
end loop;
end;
/
--这里要记得先预先执行一遍，将过程创建起来！
接下来：
drop table t purge;
create table t(x int);
alter system flush shared_pool;
set timing on
exec proc3;
Elapsed: 00:00:06.25
原因proc3也是实现了绑定变量，而动态SQL的特点是执行过程中再解析，而静态SQL的特点是编译的过程就解析好了。这也是提升速度的原因。
4.批量提交，再次加速（注意看commit与上面commit位置的不同）
create or replace procedure proc4
as
begin
for i in 1..100000
loop
insert into t values(i);
end loop;
commit;
end;
/
接下来：
drop table t purge;
create table t(x int);
set timing on
SCOTT@ PROD>exec proc4;
PL/SQL procedure successfully completed.
Elapsed: 00:00:01.69
5.集合的写法，飞跃的提速
drop table purge;
create table t(x int);
alter system flush shared_pool;
set timing on
insert into t select rownum from dual connect by level<=100000;
99999 rows created.
Elapsed: 00:00:00.09
6.直接路径方法加速
drop table t purge;
alter system flush shared_pool;
set timing on
create table t as select rownum x from dual connect by level<=10000000;
用时：00:00:10.14（这里我有改为添加1000万数据）
这里真正原因是insert into t select 的方式是将数据先写到data buffer中，然后再刷到磁盘中，而create table t 的方式却是跳过了数据缓冲区，直接写进磁盘中，这种方式又称直接路径读写方式，因为原本是数据先到内存，再到磁盘，更改为直接到磁盘，少了个步骤，因而速度提升了许多。
直接路径读写方式的缺点在于由于数据不经过数据缓冲区，所以在数据缓冲区中一定读不到这些数据，因此一定会有物理读，但是在很多时候，尤其是在海量数据需要迁移插入时，快速插入才是真正的第一目的，该表一般记录巨大，data buffer 甚至还装不下其十分之一，百分之一，这些共享内存的数据意义不大，这是我们一般会选择直接路径读写方式来完成海量数据的插入。
7.并行设置，无敌加速
drop table t purge;
alter system flush shared_pool;
set timing on
create table t nologging parallel 64 as select rownum x from dual connect by level<=10000000;
不过并行最大的特点就是占用了大多数CPU的资源，如果是一个并发环境，很多应用在跑，因为这个影响课别的应用，导致别的应用资源不足，将引起很多严重问题，所以要三思后行，连接清除该机器是否允许你这样占用全部的资源。