`
king_tt
  • 浏览: 2083975 次
  • 性别: Icon_minigender_1
  • 来自: 深圳
社区版块
存档分类
最新评论

fetch bulk collect into 批量效率的读取游标数据

 
阅读更多
通常我们获取游标数据是用 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 获取游标数据

Java代码
  1. declare    
  2.   --声明需要集合类型及变量,参照字段的type来声明类型      
  3.  typeid_typeistableofsr_contacts.sr_contact_id%type;   
  4.  v_idid_type;            
  5.  typephone_typeistableofsr_contacts.contact_phone%type;   
  6.  v_phonephone_type;            
  7.  typeremark_typeistableofsr_contacts.remark%type;     
  8.  v_remarkremark_type;   
  9.   cursorall_contacts_curisselectsr_contact_id,contact_phone,remarkfromsr_contactswhererownum<=100000;      
  10. begin    
  11.    openall_contacts_cur;     
  12.    loop     
  13.        fetchall_contacts_curbulkcollectintov_id,v_phone,v_remarklimit256;     
  14.        foriin1..v_id.countloop--遍历集合     
  15.     --用v_id(i)/v_phone(i)/v_remark(i)取出字段值来执行你的业务逻辑       
  16.        endloop;     
  17.        exitwhenall_contacts_cur%notfound;--exit不能紧接fetch了,不然会漏记录     
  18.    endloop;     
  19.    closeall_contacts_cur;     
  20. end;   

 

  使用 fetch into 逐行获取游标数据

  1. declare 
  2.   --声明变量,参照字段的type来声明类型   
  3.  v_idsr_contacts.sr_contact_id%type;  
  4.  v_phonesr_contacts.contact_phone%type;  
  5.  v_remarksr_contacts.remark%type;   
  6.   cursorall_contacts_curisselectsr_contact_id,contact_phone,remarkfromsr_contactswhererownum<=100000;   
  7. begin 
  8.       
  9.    openall_contacts_cur;  
  10.    loop  
  11.        fetchall_contacts_curintov_id,v_phone,v_remark;  
  12.        exitwhenall_contacts_cur%notfound;      
  13.        --用v_id/v_phone/v_remark取出字段值来执行你的业务逻辑  
  14.        null;--这里只放置一个空操作,只为测试循环取数的效率  
  15.    endloop;  
  16.    closeall_contacts_cur;  
  17. 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  属性来进行遍历。

  1. declare 
  2.   --声明需要集合类型及变量,参照字段的type来声明类型   
  3.  typecontacts_typeistableofsr_contacts%rowtype;  
  4.  v_contactscontacts_type;   
  5.   cursorall_contacts_curis--用rownum来限定取出的记录数来测试   
  6.     select*fromsr_contactswhererownum<=10000;   
  7. begin 
  8.       
  9.    openall_contacts_cur;  
  10.    loop  
  11.        fetchall_contacts_curbulkcollectintov_contactslimit256;  
  12.        foriinv_contacts.first..v_contacts.lastloop--遍历集合  
  13.            --用v_contacts(i).sr_contact_id/v_contacts(i).contact_phone/v_contacts(i).remark  
  14.            --的形式来取出各字段值来执行你的业务逻辑   
  15.        endloop;  
  16.        exitwhenall_contacts_cur%notfound;  
  17.    endloop;  
  18.    closeall_contacts_cur;  
  19. 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 部分可写成:



  1. begin 
  2.    openall_contacts_cur;  
  3.    fetchall_contacts_curbulkcollectintov_contacts;  
  4.    foriinv_contacts.first..v_contacts.lastloop--遍历集合  
  5.        --用v_contacts(i).sr_contact_id/v_contacts(i).contact_phone/v_contacts(i).remark  
  6.        --的形式来取出各字段值来执行你的业务逻辑  
  7.        null;--这里只放置一个空操作,只为测试循环取数的效率  
  8.        dbms_output.put_line(2000);  
  9.    endloop;  
  10.    closeall_contacts_cur;  
  11. 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 操作
分享到:
评论

相关推荐

    Oracle 中使用 fetch bulk collect into 批量效率的读取游标数据

    Oracle 中使用 fetch bulk collect into 批量效率的读取游标数据

    oracle批量处理(bulk collect)

    采用bulk collect可以将查询结果一次性地加载到collections中。...可以在select into,fetch into,returning into语句使用bulk collect。注意在使用bulk collect时,所有的into变量都必须是collections.

    Oracle 遍历游标的四种方式汇总(for、fetch、while、BULK COLLECT)

     Oracle 遍历游标的四种方式(for、fetch、while、bulk collect+forall) 2.问题分析  我们可以把游标想象成一张表,想要遍历游标,就要取到游标的每行数据,所以问题的关键就成了:如何取到行数据? 3.解决方案 ...

    oracle下巧用bulk collect实现cursor批量fetch的sql语句

    在一般的情况下,使用批量fetch的几率并不是很多,但是Oracle提供了这个功能我们最好能熟悉一下,说不定什么时候会用上它。 代码如下:declare cursor c1 is select * from t_depart; v_depart t_depart%...

    利用游标遍历数据表或者数据集

    declare cur cursor for select AccountID,EmployeeName from #Temp ... fetch next from cur into @tmpID, @tmpName fetch next from cur into @tmpID, @tmpName end close cur deallocate cur

    SQL Server 游标的简单使用

    SQL Server对游标的使用要遵循:声明游标–打开游标–读取数据–关闭游标–删除游标。下面让我们来看看几种常用游标是怎么使用的! 1、只读游标的使用(只能使用next提取数据) --声明一个只读游标 declare cur_stu ...

    存储过程 游标

    显式游标的用法 声明游标 CURSOR 游标名 IS SELECT语句FROM语句; 打开游标 OPEN 游标名; 提取游标 FETCH 游标名 INTO 变量列表; 关闭游标 CLOSE 游标名; 完整示例

    关于游标使用sql

    测试3条数据 CREATE TABLE test_main ( id INT, value VARCHAR(10), PRIMARY KEY(id) ); INSERT INTO test_main(id, value) VALUES (1, 'ONE'); INSERT INTO test_main(id, value) VALUES (2, 'TWO'); ...

    MSSQL 游标使用 心得

    游标为您提供了在逐行的基础上而不是一次处理整个结果集为基础的操作表中数据的方法。 1.如何使用游标 1)定义游标语句 Declare &lt;游标名&gt; Cursor For 2)创建游标语句 Open &lt;游标名&gt; 3)提取游标列值、移动...

    Oracle中PLSQL游标

    Oracle中PLSQL游标简介

    sql server游标的使用

    --读取数据(使用FETCH来获取该记录的值;FETCH会执行两步操作:首先将游标当前指向的记录保存到一个局部变量中, --然后游标将自动移向下一条记录) --将一条或多条记录读入某个局部变量后,就可以根据需要对其进行...

    MySQL游标概念与用法详解

    本文实例讲述了MySQL游标概念与用法。分享给大家供大家参考,具体如下: 1、游标的概念(Cursor) 一条sql,对应N条资源,取出资源的接口,就是游标,沿着游标,可以一次取出1行。...fetch 游标名 into var1,var2[,.

    sql游标实例,更新行

    Fetch Next From subgroup Into @EntryID , @BillNo , @Number , @qty , @SPID While @@FETCH_STATUS = 0 Begin re: set @DID = 0 set @DID = (Select min(FDetailID) From tmp_Batch Where FNumber = @Number And...

    SQL编程中的游标的使用方法

    以次在表 test1中 读取a , b 两列的值 DECLARE @a varchar(n) --这里的类型要换成跟你的a字段的类型一样的 DECLARE @b varchar(n) --这里的类型要换成跟你的b字段的类型一样的...FETCH NEXT FROM bCursor INTO @a,@b

    当时自己初学SQL时,对游标很难理解,现在编写了一个实例游标循环,到时写SQL循环时方便些

    当时自己初学SQL时,对游标很难理解,现在编写了一个实例游标循环,到时写...fetch next from cursor1 into @id,@name -- 定义一个游标有两个变量 while @@fetch_status=0 --返回值成功的时候循环 以上是我个人的理解

    fetch-california-housing()数据集

    fetch_california_housing()数据集

    游标与全文检索

    游标与全文检索;PPT 授课电子讲义

    Oracle存储过程游标用法分析

    fetch语句将列的指读取到指定的变量中; 语法: fetch cursor_name into variable[, variable ...]; 例子: create or replace procedure sel_person is v_id person.id%type; v_name person.

    sql 游标、存储过程

    --读取第一行数据(将 t_order_temp 表中的 dish_id 放到 @DishId 变量中) WHILE @@FETCH_STATUS=0 BEGIN UPDATE t_dishes SET ordered_number = ordered_number + @OrderNum WHERE id = @DishId; --更新数据 ...

Global site tag (gtag.js) - Google Analytics