在我们的系统里,大家在写pl/sql时,处理多条记录时,几乎都是通过游标来完成的,这样是非常影响性能的。我们可以用批量绑定可以大大的改善。
批量绑定是oracle9i增加的特性,是指执行单次sql操作能传递所有集合元素的数据。通过绑定绑定变量可以极大的提高数据处理速度,提高应用程序的速度。批处理可以用与select,update,delete,insert语句上进行批量数据的处理。
在我们写pl/sql的时候,oracle会为select和dml语句分配上下文区(这个步骤是非常耗资源的,oracle对于太频繁的切换,都换用其它方式代替,例如spin),游标就是上下文区的指针。所以在我们日常coding时,尽量少用cursor,虽然cursor使用很简单,但也带来很大的性能问题,我们现在系统里的游标就非常多。
批量绑定是使用bulk collect和forall语句来完成的。
bulk collect:用与取得批量数据,只能用户,select,fetch和dml返回字句
forall:适用于批量的dml
下面简单介绍下使用批量绑定和不使用批量绑定的性能对比的样例,一共两个例子:
测试表:
create table TESTA
(
ID NUMBER(6) primary key not null ,
NAME VARCHAR2(10)
)
**********************************************************************************
例子1:
Forall:
使用批量绑定:
SQL> declare
2 type id_table_type is table of number(6) index by binary_integer;
3 type name_table_type is table of varchar2(10) index by binary_integer;
4
5 id_table id_table_type;
6 name_table name_table_type;
7 start_time number(10);
8 end_time number(10);
9
10 begin
11
12 for i in 1..5000 loop
13 id_table(i):=i;
14 name_table(i):='name'||to_char(i);
15 end loop;
16
17 start_time:=dbms_utility.get_time;
18 for i in 1..id_table.count loop
19 insert into testa values(id_table(i),id_table(i)) ;
20 end loop;
21 end_time:=dbms_utility.get_time;
22 dbms_output.put_line('总的执行时间(s):'|| (end_time-start_time)/100) ;
23 end;
24 /
总的执行时间(s):.32
PL/SQL procedure successfully completed
Executed in 0.344 seconds
未使用批量绑定:
SQL> declare
2 type id_table_type is table of number(6) index by binary_integer;
3 type name_table_type is table of varchar2(10) index by binary_integer;
4
5 id_table id_table_type;
6 name_table name_table_type;
7 start_time number(10);
8 end_time number(10);
9
10 begin
11
12 for i in 1..5000 loop
13 id_table(i):=i;
14 name_table(i):='name'||to_char(i);
15 end loop;
16
17 start_time:=dbms_utility.get_time;
18 --for i in 1..id_table.count loop
19 forall i in 1..id_table.count
20 insert into testa values(id_table(i),id_table(i)) ;
21 --end loop;
22 end_time:=dbms_utility.get_time;
23 dbms_output.put_line('总的执行时间(s):'|| (end_time-start_time)/100) ;
24 end;
25 /
总的执行时间(s):.02
PL/SQL procedure successfully completed
Executed in 0.047 seconds
SQL>
对比:
批量绑定 :Executed in 0.344 seconds
非批量绑定:Executed in 0.047 seconds
结果:性能提高近8倍
**************************************************************************************************************
例子2:
bulk collect:
未批量绑定(取100条记录):
SQL> declare
2 type testa_table_type is table of testa%rowtype index by binary_integer;
3 type id_table_type is table of number(6) index by binary_integer;
4 type name_table_type is table of varchar2(10) index by binary_integer;
5
6 cursor cur_testa is
7 select * from testa where rownum <=#
8
9 id_table id_table_type;
10 name_table name_table_type;
11 testa_table testa_table_type;
12
13 v_id number(6);
14 v_name varchar2(10);
15
16 begin
17 for i in 1..5000 loop
18 id_table(i):=i;
19 name_table(i):='name'||to_char(i);
20 end loop;
21 forall i in 1..id_table.count
22 insert into testa values(id_table(i),id_table(i)) ;
23
24 -- select * bulk collect into testa_table from testa where rownum <=#
25 open cur_testa;
26 loop
27 fetch cur_testa into v_id,v_name;
28 exit when cur_testa%notfound;
29 --for i in 1..testa_table.count loop
30 dbms_output.put_line('name is :'||v_name) ;
31 end loop;
32 close cur_testa;
33 end;
34 /
name is :2162
...
name is :2261
PL/SQL procedure successfully completed
Executed in 0.047 seconds
批量绑定(取100条记录):
SQL> declare
2 type testa_table_type is table of testa%rowtype index by binary_integer;
3 type id_table_type is table of number(6) index by binary_integer;
4 type name_table_type is table of varchar2(10) index by binary_integer;
5
6 id_table id_table_type;
7 name_table name_table_type;
8 testa_table testa_table_type;
9
10 begin
11 for i in 1..5000 loop
12 id_table(i):=i;
13 name_table(i):='name'||to_char(i);
14 end loop;
15 forall i in 1..id_table.count
16 insert into testa values(id_table(i),id_table(i)) ;
17
18 select * bulk collect into testa_table from testa where rownum <=#
19 for i in 1..testa_table.count loop
20 dbms_output.put_line('name is :'||testa_table(i).name) ;
21 end loop;
22 end;
23 /
name is :2162
..
name is :2261
PL/SQL procedure successfully completed
Executed in 0.031 seconds
对比:
批量绑定 :Executed in 0.031 seconds
非批量绑定:Executed in 0.047 seconds
结果:性能提高近2倍
*****************************************************************************
我这里只是简单介绍下批量绑定,具体使用方法就需要大家自己练习了。
和批量绑定有关的知识如下:
一.oracle的数据类型分为几大类:标量(scalar),复合(composite),引用(reference)和lob
1.标量(scalar)
合法的标量类型与数据库的列所使用的类型相同,也就是我常用的数据类型,
它又分为七个组:数字、字符、行、日期、行标识、布尔和可信。
2.复合(composite)
标量类型是经过预定义的,利用这些类型可以衍生出一些复合类型(集合)。主要有记录、表。
3.引用(reference)
它指向一个对象,例如游标就是引用
4.lob
LOB变量主要是用来存储大量数据的数据库字段
二.在处理复合类型的变量,oracle有对应的方法
oracle在pl/sql里处理数据的方法:
单行单列:用标量来存储
单行多列:用pl/sql记录来存储
多行单列:用pl/sql集合(索引表,嵌套表,varrary)
多行多列:pl/sql记录表
------end-----