采用bulk collect可以将查询结果一次性地加载到collections中,而不是通过cursor一条一条地处理。
使用 fetch into 逐行获取游标数据
执行性能比较
看看测试的结果,分别执行五次所耗费的秒数:
当 rownum <= 100000 时:
fetch bulk collect into 耗时:0.125秒, 0.125秒, 0.125秒, 0.125秒, 0.141秒
fetch into 耗时: 1.266秒, 1.250秒, 1.250秒, 1.250秒, 1.250秒
当 rownum <= 1000000 时:
fetch bulk collect into 耗时:1.157秒, 1.157秒, 1.156秒, 1.156秒, 1.171秒
fetch into 耗时: 12.128秒, 12.125秒, 12.125秒, 12.109秒, 12.141秒
当 rownum <= 10000 时:
fetch bulk collect into 耗时:0.031秒, 0.031秒, 0.016秒, 0.015秒, 0.015秒
fetch into 耗时: 0.141秒, 0.140秒, 0.125秒, 0.141秒, 0.125秒
当 rownum <= 1000 时:
fetch bulk collect into 耗时:0.016秒, 0.015秒, 0.016秒, 0.016秒, 0.015秒
fetch into 耗时: 0.016秒, 0.031秒, 0.031秒, 0.032秒, 0.015秒
从测试结果来看游标的记录数越大时,用 fetch bulk collect into 的效率很明显示,趋于很小时就差不多了。
注意了没有,前面使用 fetch bulk collect into 时前为每一个查询列都定义了一个集合,这样有些繁琐。我们之前也许用过表的 %rowtype 类型,同样的我们也可以定义表的 %rowtype 的集合类型。看下面的例子,同时在这个例子中,我们借助于集合的 first、last 属性来代替使用 count 属性来进行遍历。
关于 limit 参数
你可以根据你的实际来调整 limit 参数的大小,来达到你最优的性能。limit 参数会影响到 pga 的使用率。而且也可以在 fetch bulk 中省略 limit 参数,写成
fetch all_contacts_cur bulk collect into v_contacts;
有些资料中是说,如果不写 limit 参数,将会以数据库的 arraysize 参数值作为默认值。在 sqlplus 中用 show arraysize 可以看到该值默认为 15,set arraysize 256 可以更改该值。而实际上我测试不带 limit 参数时,外层循环只执行了一轮,好像不是 limit 15,所以不写 limit 参数时,可以去除外层循环,begin-end 部分可写成:
bulk collect 的其他用法(总是针对集合)
select into 语句中,如:
SELECT sr_contact_id,contact_phone BULK COLLECT INTO v_id,v_phone
FROM sr_contacts WHERE ROWNUM <= 100;
dbms_output.put_line('Count:'||v_id.count||', First:'||v_id(1)||'|'||v_phone(1));
returning into 语句中,如:
DELETE FROM sr_contacts WHERE sr_contact_id < 30
RETURNING sr_contact_id, contact_phone BULK COLLECT INTO v_id, v_phone;
dbms_output.put_line('Count:'||v_id.count||', First:'||v_id(1)||'|'||v_phone(1));
forall 的 bulk dml 操作,它大大优于 for 集合后的操作
fetch all_contacts_cur bulk collect into v_contacts;
forall i in 1 .. v_contacts.count
--forall i in v_contacts.first .. v_contacts.last
--forall i in indices of v_contacts --10g以上,可以是非连续的集合
insert into sr_contacts(sr_contact_id,contact_phone,remark)
values(v_contacts(i).sr_contact_id,v_contacts(i).contact_phone,v_contacts(i).remark);
--或者是单条的 delete/update 操作
可以在select into,fetch into,returning into语句使用bulk collect。
注意:在使用bulk collect时,所有的into变量都必须是collections
create table t_test as
select object_id, object_name, object_type
from dba_objects
where wner = 'TEST';
1、在select into语句中使用bulk collect
declare
type object_list is table of t_test.object_name%type;
objs object_list;
begin
select object_name bulk collect
into objs
from t_test
where rownum <= 100;
for r in objs.first .. objs.last loop
dbms_output.put_line(' objs(r)=' || objs(r));
end loop;
end;
/
2、在fetch into中使用bulk collect
declare
type objecttab is table of t_test%rowtype;
objs objecttab;
cursor cob is
select object_id, object_name, object_type
from t_test
where rownum <= 10;
begin
open cob;
fetch cob bulk collect
into objs;
close cob;
for r in objs.first .. objs.last loop
dbms_output.put_line(' objs(r)=' || objs(r).object_name);
end loop;
end;
/
以上为把结果集一次fetch到collect中,我们还可以通过limit参数,来分批fetch数据,如下:
declare
type objecttab is table of t_test%rowtype;
objs objecttab;
cursor cob is
select object_id, object_name, object_type
from t_test
where rownum <= 10000;
begin
open cob;
loop
fetch cob bulk collect
into objs limit 1000;
exit when cob%notfound;
dbms_output.put_line('count:' || objs.count || ' first:' || objs.first ||
' last:' || objs.last);
for r in objs.first .. objs.last loop
dbms_output.put_line(' objs(r)=' || objs(r).object_name);
end loop;
end loop;
close cob;
end;
/
你可以根据实际来调整limit参数的大小,来达到最优的性能。limit参数会影响到PGA的使用率。
3、在returning into中使用bulk collect
declare
type id_list is table of t_test.object_id%type;
ids id_list;
type name_list is table of t_test.object_name%type;
names name_list;
begin
delete from t_test
where object_id <= 87510 returning object_id, object_name bulk collect into ids,
names;
dbms_output.put_line('deleted ' || sql%rowcount || ' rows:');
for i in ids.first .. ids.last loop
dbms_output.put_line('object #' || ids(i) || ': ' || names(i));
end loop;
end;
4.用于动态语句
declare
v_query_sql varchar2(500);
type type_emp_table is table of emp%rowtype index by binary_integer;
v_emp_table type_emp_table;
begin
v_query_sql := 'select * from emp';
execute immediate v_query_sql bulk collect into v_emp_table;
forall i in 1..v_emp_table.count
insert into emp_bak values v_emp_table(i);
end;
declare
v_query_sql varchar2(500);
type type_emp_table is table of emp%rowtype index by binary_integer;
v_emp_table type_emp_table;
type type_ename_table is table of emp.ename%type index by binary_integer;
v_ename_table type_ename_table;
begin
update emp set flag=1 where deptno=20 returning flag bulk collect into v_ename_table;
for i in 1..v_ename_table.count loop
dbms_output.put_line(v_ename_table(i));
end loop;
end;
<pre class="sql" name="code">--------------------------------------------------------------<span style="font-family: Arial, Helvetica, sans-serif;">--------------------------------------------------------------</span>
<pre class="sql" name="code">--------------------------------------------------------------<span style="font-family: Arial, Helvetica, sans-serif;">--------------------------------------------------------------</span>
<h1 class="postTitle"><a target=_blank class="postTitle2" id="cb_post_title_url" href="http://www.cnblogs.com/haibin168/archive/2011/02/27/1966072.html">fetch bulk collect into 批量效率的读取游标数据 【转载】</a></h1>通常我们获取游标数据是用 fetch some_cursor into var1, var2 的形式,当游标中的记录数不多时不打紧。然而自 Oracle 8i 起,Oracle 为我们提供了 fetch bulk collect 来批量取游标中的数据。它能在读取游标中大量数据的时候提高效率,就像 SNMP 协议中,V2 版比 V1 版新加了 GET-BULK PDU 一样,也是用来更高效的批量取设备上的节点值。
<strong><span style="color:#ff0000;">fetch bulk collect into</span></strong> 的使用格式是:<strong style="background-color: rgb(255, 102, 102);">fetch</strong> some_cursor <strong style="background-color: rgb(255, 102, 102);">collect into</strong> col1, col2 <strong style="background-color: rgb(255, 102, 102);">limit</strong> xxx。col1、col2 是声明的集合类型变量,xxx 为每次取数据块的大小(记录数),相当于缓冲区的大小,可以不指定 limit xxx 大小。下面以实际的例子来说明它的使用,并与逐条取记录的 fetch into 执行效率上进行比较。测试环境是 Oracle 10g 10.2.1.0,查询的联系人表 sr_contacts 中有记录数 1802983 条,游标中以 rownum 限定返回的记录数。
使用 fetch bulk collect into 获取游标数据
<div><div>PLSQL代码 </div><ol start="1"><li>declare </li><li> --声明需要集合类型及变量,参照字段的 type 来声明类型 </li><li> type id_type is table of sr_contacts.sr_contact_id%type; </li><li> v_id id_type; </li><li> type phone_type is table of sr_contacts.contact_phone%type; </li><li> v_phone phone_type; </li><li> type remark_type is table of sr_contacts.remark%type; </li><li> v_remark remark_type; </li><li> </li><li> cursor all_contacts_cur is select sr_contact_id,contact_phone,remark from sr_contacts where rownum <= 100000; </li><li> </li><li>begin </li><li> open all_contacts_cur; </li><li> loop </li><li> fetch all_contacts_cur bulk collect into v_id,v_phone,v_remark limit 256; </li><li> for i in 1..v_id.count loop --遍历集合 </li><li> --用 v_id(i)/v_phone(i)/v_remark(i) 取出字段值来执行你的业务逻辑 </li><li> end loop; </li><li> exit when all_contacts_cur%notfound; --exit 不能紧接 fetch 了,不然会漏记录 </li><li> end loop; </li><li> close all_contacts_cur; </li><li>end; </li></ol></div><pre style="display: none;" name="code">declare --声明需要集合类型及变量,参照字段的 type 来声明类型 type id_type is table of sr_contacts.sr_contact_id%type; v_id id_type; type phone_type is table of sr_contacts.contact_phone%type; v_phone phone_type; type remark_type is table of sr_contacts.remark%type; v_remark remark_type; cursor all_contacts_cur is select sr_contact_id,contact_phone,remark from sr_contacts where rownum <= 100000; begin open all_contacts_cur; loop fetch all_contacts_cur bulk collect into v_id,v_phone,v_remark limit 256; for i in 1..v_id.count loop --遍历集合 --用 v_id(i)/v_phone(i)/v_remark(i) 取出字段值来执行你的业务逻辑 end loop; exit when all_contacts_cur%notfound; --exit 不能紧接 fetch 了,不然会漏记录 end loop; close all_contacts_cur; end;
使用 fetch into 逐行获取游标数据
- declare
- --声明变量,参照字段的 type 来声明类型
- v_id sr_contacts.sr_contact_id%type;
- v_phone sr_contacts.contact_phone%type;
- v_remark sr_contacts.remark%type;
- cursor all_contacts_cur is select sr_contact_id ,contact_phone,remark from sr_contacts where rownum <= 100000;
- begin
- open all_contacts_cur;
- loop
- fetch all_contacts_cur into v_id,v_phone,v_remark;
- exit when all_contacts_cur%notfound;
- --用 v_id/v_phone/v_remark 取出字段值来执行你的业务逻辑
- null; --这里只放置一个空操作,只为测试循环取数的效率
- end loop;
- close all_contacts_cur;
- end;
执行性能比较
看看测试的结果,分别执行五次所耗费的秒数:
当 rownum <= 100000 时:
fetch bulk collect into 耗时:0.125秒, 0.125秒, 0.125秒, 0.125秒, 0.141秒
fetch into 耗时: 1.266秒, 1.250秒, 1.250秒, 1.250秒, 1.250秒
当 rownum <= 1000000 时:
fetch bulk collect into 耗时:1.157秒, 1.157秒, 1.156秒, 1.156秒, 1.171秒
fetch into 耗时: 12.128秒, 12.125秒, 12.125秒, 12.109秒, 12.141秒
当 rownum <= 10000 时:
fetch bulk collect into 耗时:0.031秒, 0.031秒, 0.016秒, 0.015秒, 0.015秒
fetch into 耗时: 0.141秒, 0.140秒, 0.125秒, 0.141秒, 0.125秒
当 rownum <= 1000 时:
fetch bulk collect into 耗时:0.016秒, 0.015秒, 0.016秒, 0.016秒, 0.015秒
fetch into 耗时: 0.016秒, 0.031秒, 0.031秒, 0.032秒, 0.015秒
从测试结果来看游标的记录数越大时,用 fetch bulk collect into 的效率很明显示,趋于很小时就差不多了。
注意了没有,前面使用 fetch bulk collect into 时前为每一个查询列都定义了一个集合,这样有些繁琐。我们之前也许用过表的 %rowtype 类型,同样的我们也可以定义表的 %rowtype 的集合类型。看下面的例子,同时在这个例子中,我们借助于集合的 first、last 属性来代替使用 count 属性来进行遍历。
- declare
- --声明需要集合类型及变量,参照字段的 type 来声明类型
- type contacts_type is table of sr_contacts%rowtype;
- v_contacts contacts_type;
- cursor all_contacts_cur is --用 rownum 来限定取出的记录数来测试
- select * from sr_contacts where rownum <= 10000;
- begin
- open all_contacts_cur;
- loop
- fetch all_contacts_cur bulk collect into v_contacts limit 256;
- for i in v_contacts.first .. v_contacts.last loop --遍历集合
- --用 v_contacts(i).sr_contact_id/v_contacts(i).contact_phone/v_contacts(i).remark
- --的形式来取出各字段值来执行你的业务逻辑
- end loop;
- exit when all_contacts_cur%notfound;
- end loop;
- close all_contacts_cur;
- end;
关于 limit 参数
你可以根据你的实际来调整 limit 参数的大小,来达到你最优的性能。limit 参数会影响到 pga 的使用率。而且也可以在 fetch bulk 中省略 limit 参数,写成
fetch all_contacts_cur bulk collect into v_contacts;
有些资料中是说,如果不写 limit 参数,将会以数据库的 arraysize 参数值作为默认值。在 sqlplus 中用 show arraysize 可以看到该值默认为 15,set arraysize 256 可以更改该值。而实际上我测试不带 limit 参数时,外层循环只执行了一轮,好像不是 limit 15,所以不写 limit 参数时,可以去除外层循环,begin-end 部分可写成:
- begin
- open all_contacts_cur;
- fetch all_contacts_cur bulk collect into v_contacts;
- for i in v_contacts.first .. v_contacts.last loop --遍历集合
- --用 v_contacts(i).sr_contact_id/v_contacts(i).contact_phone/v_contacts(i).remark
- --的形式来取出各字段值来执行你的业务逻辑
- null; --这里只放置一个空操作,只为测试循环取数的效率
- dbms_output.put_line(2000);
- end loop;
- close all_contacts_cur;
- end;
bulk collect 的其他用法(总是针对集合)
select into 语句中,如:
SELECT sr_contact_id,contact_phone BULK COLLECT INTO v_id,v_phone
FROM sr_contacts WHERE ROWNUM <= 100;
dbms_output.put_line('Count:'||v_id.count||', First:'||v_id(1)||'|'||v_phone(1));
returning into 语句中,如:
DELETE FROM sr_contacts WHERE sr_contact_id < 30
RETURNING sr_contact_id, contact_phone BULK COLLECT INTO v_id, v_phone;
dbms_output.put_line('Count:'||v_id.count||', First:'||v_id(1)||'|'||v_phone(1));
forall 的 bulk dml 操作,它大大优于 for 集合后的操作
fetch all_contacts_cur bulk collect into v_contacts;
forall i in 1 .. v_contacts.count
--forall i in v_contacts.first .. v_contacts.last
--forall i in indices of v_contacts --10g以上,可以是非连续的集合
insert into sr_contacts(sr_contact_id,contact_phone,remark)
values(v_contacts(i).sr_contact_id,v_contacts(i).contact_phone,v_contacts(i).remark);
--或者是单条的 delete/update 操作