- 浏览: 250959 次
- 性别:
- 来自: 北京
文章分类
最新评论
-
Orc3Ming:
哦,脚本的解释路径,不是注释。
RHEL6.4更改为CentOS6.4的yum源 -
Orc3Ming:
将第一行 #!/usr/bin/python 修改为 #!/u ...
RHEL6.4更改为CentOS6.4的yum源 -
anniecharlotte:
请问:如果登录某个节点
执行nodetool ring查看集群 ...
Cassandra集群 -
Q_Qkings8:
给了我好的提示,已经成功的解决了问题
Eclipse Debug Source not found for -
kevin.wang:
feijin1981 写道我也有同样的问题,但是Window ...
Eclipse Debug Source not found for
通常我们获取游标数据是用 fetch some_cursor into var1, var2 的形式,当游标中的记录数不多时不打紧。然而自 Oracle 8i 起,Oracle 为我们提供了 fetch bulk collect 来批量取游标中的数据。它能在读取游标中大量数据的时候提高效率,就像 SNMP 协议中,V2 版比 V1 版新加了 GET-BULK PDU 一样,也是用来更高效的批量取设备上的节点值。
fetch bulk collect into 的使用格式是:fetch some_cursor collect into col1, col2 limit xxx。col1、col2 是声明的集合类型变量,xxx 为每次取数据块的大小(记录数),相当于缓冲区的大小,可以不指定 limit xxx 大小。下面以实际的例子来说明它的使用,并与逐条取记录的 fetch into 执行效率上进行比较。测试环境是 Oracle 10g 10.2.1.0,查询的联系人表 sr_contacts 中有记录数 1802983 条,游标中以 rownum 限定返回的记录数。
使用 fetch bulk collect into 获取游标数据
使用 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 操作
fetch bulk collect into 的使用格式是:fetch some_cursor collect into col1, col2 limit xxx。col1、col2 是声明的集合类型变量,xxx 为每次取数据块的大小(记录数),相当于缓冲区的大小,可以不指定 limit xxx 大小。下面以实际的例子来说明它的使用,并与逐条取记录的 fetch into 执行效率上进行比较。测试环境是 Oracle 10g 10.2.1.0,查询的联系人表 sr_contacts 中有记录数 1802983 条,游标中以 rownum 限定返回的记录数。
使用 fetch bulk collect into 获取游标数据
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 操作
发表评论
-
connect by prior
2012-01-29 14:41 745http://www.blogjava.net/freeman ... -
Oracle 解锁
2011-08-22 11:32 835在for update 查询时,线程被阻塞 查询阻塞线程: ... -
exists与in
2011-07-27 20:53 10111. exists: 不缓存exists() ... -
outer join , inner join , cross join
2010-11-30 13:18 887select * from table1; ID NAME 1 ... -
查看Oracle表空间 tablespace
2010-10-29 09:32 11401. 查看所有表空间大小 SQL> select ta ... -
Oracle--create user& tablespace
2010-10-29 09:22 2606-建立表空间(oracle中的tablespace(表空间)就 ... -
win7下安装Oracle10g
2010-10-03 01:01 1083解决方案: 步骤一:在解压出的oracle文件夹中搜 ... -
Oracle 关于密码及密码文件
2010-09-29 10:49 1616以安装Oracle的用户进入系统: 忘记密码: 1.在命令行下 ... -
修改Oracle端口
2010-09-02 19:20 1400<script>function StorePag ... -
关于Oracle 10g 默认用户、密码及登录问题
2010-09-02 19:02 1960Oracle 10g 默认用户、密码 参见http://sap ... -
Oracle 之 Sequence
2010-08-13 09:01 8231、Create Sequence 你首先要有CREATE ... -
存储过程中 调用 Java
2010-08-12 11:39 891一、如何创建java存储过程? 通常有三种方法来创建java存 ... -
Java 调用Oracle 存储过程
2010-08-12 10:45 16301、存储过程是指保存在 ... -
Java 调用Oracle 函数
2010-08-12 10:26 1543oracle的存储过程和函数区别 1、都可以使用out参数返 ... -
Merge Into
2010-08-12 10:22 1366用途 merge 命令可以用 ... -
Returnning Into 总结
2010-08-12 09:45 1094ORACLE的DML语句中可以指定RETURNING语句。使用 ... -
Pro * C 的使用(三)
2010-08-11 18:05 1106(7) 动态定义语句 SQL语句分动态定义语句和静态 ... -
Pro * C 的使用(二)
2010-08-11 18:02 13622.应用程序体 在Pro*C ... -
Pro * C 的使用(一)
2010-08-11 17:59 1176一 Pro*C 程序概述: ... -
ref cursor
2010-08-11 16:56 1028REF CURSOR 小结 利用REF CURSOR,可以在程 ...
相关推荐
Oracle 中使用 fetch bulk collect into 批量效率的读取游标数据 Oracle 中的游标数据读取是数据库开发中非常常见的操作。通常,我们使用 fetch some_cursor into var1, var2 形式来获取游标数据,当游标中的记录数...
默认情况下,`FETCH`语句会读取一行数据,但在高级用法中,我们可以结合`BULK COLLECT INTO`语句和`LIMIT`子句,一次读取多行数据,即实现分批读取。 ### 分批取数据示例解析 下面,我们通过分析给定的部分内容,...
`BULK COLLECT`允许一次性从游标中获取多行数据,并存储到集合中,这可以显著提高性能。 首先定义一个集合类型: ```sql TYPE type_name IS TABLE OF data_type; ``` 接着定义一个该类型的变量: ```sql ...
3. 批量读取:为了提高效率,可以使用BULK COLLECT INTO语句批量获取多条数据,然后一次性存储到变量或集合中。 总之,游标是数据库编程中的重要工具,尤其在需要逐行处理数据时,游标提供了强大的控制力和灵活性,...
使用游标批量收集数据 为了提高效率,我们可以使用游标(Cursor)批量收集数据。下面是一个示例代码: ```sql DECLARE CURSOR c1 IS SELECT * FROM cuX.cuX_test_temp; -- 定义游标 TYPE tbl_type IS TABLE ...
fetch c_bulk bulk collect into ref_bulk limit 250000; -- 执行批量插入操作 forall i in 1..ref_bulk.count insert into emp_table_his values ref_bulk(i); commit; exit when c_bulk%notfound; end loop...
FETCH CUR_ROWID BULK COLLECT INTO V_ROWID_ARRAY LIMIT 10000; ``` - 使用 `BULK COLLECT` 将游标中的结果一次性读取到 `V_ROWID_ARRAY` 数组中,这里限制了每次最多读取10000条记录。 3. **批量删除数据**: ...
- **读取数据**:通过`FETCH`命令读取游标中的数据。 - **关闭游标**:使用`CLOSE`命令来关闭游标,释放资源。 #### 三、游标使用示例分析 ##### 示例1:基础游标使用 ```sql DECLARE CURSOR cur_tsalary IS ...
- OPEN/FETCH/CLOSE:打开、读取和关闭游标,处理游标中的数据。 4. DML操作: - UPDATE:批量更新记录,RETURNING BULK COLLECT INTO获取更新后的值。 - DELETE:批量删除记录,RETURNING BULK COLLECT INTO...
Oracle游标是数据库管理系统中的一种重要机制,它允许应用程序按需逐行处理查询结果集,而不是一次性处理所有数据。...通过实践和了解各种游标特性,开发者可以更好地控制数据处理流程,从而提高程序的效率和响应性。
可以使用`BULK COLLECT INTO`配合`FORALL`进行批量处理,以提高性能。 10. **版本兼容性**: Oracle数据库的不同版本可能对LOB的支持有所不同,例如,新版本可能会引入新的LOB功能或优化。因此,升级数据库时要...
SELECT * BULK COLLECT INTO l_students FROM students; RETURN l_students; END; END student_pkg; ``` #### 四、总结 通过以上介绍,我们了解到Pl/SQL是一种非常强大的数据库编程语言,它不仅能够处理复杂...