`

提高游标批量读取的效率

阅读更多
通过bulk collect减少loop处理的开销

采用bulk collect可以将查询结果一次性地加载到collections中。
而不是通过cursor一条一条地处理。
注意在使用bulk collect时,所有的into变量都必须是collections. 
例子:(把一个表的数据放入集合中,然后再取出)
create or replace procedure test_bulk_collect_into
is
     type a is table of varchar2(30); --如果是一个表的好几个字段的话,可以这么写type a is table of AA(表名)%rowtype; 
     aa         a;
begin
     --假如我讲个表AA,里面插入1,2,3三条记录
     --通过SELECT把这个三条记录直接放进集合aa中
     execute immediate ' select  a from aa'  BULK COLLECT INTO aa;
    --取AA中的数据。
     for i in 1..aa.count loop
       dbms_output.put_line(i)  ;      --aa(i).a     
     end loop;    
end test_bulk_collect_into;

参考博客内容:
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.     type id_type is table of sr_contacts.sr_contact_id%type;   
4.     v_id id_type;               
5.     type phone_type is table of sr_contacts.contact_phone%type; 6.     v_phone phone_type;               
7.     type remark_type is table of sr_contacts.remark%type;     
8.     v_remark remark_type;    
9. 
10.cursor all_contacts_cur is select sr_contact_id,contact_phone,remark from sr_contacts where rownum <= 100000;        
11. 
12.begin      
13.    open all_contacts_cur;       
14.    loop       
15.        fetch all_contacts_cur bulk collect into v_id,v_phone,v_remark limit 256;       
16.        for i in 1..v_id.count loop --遍历集合       
17.     --用 v_id(i)/v_phone(i)/v_remark(i) 取出字段值来执行你的业务逻辑          
18.        end loop;       
19.        exit when all_contacts_cur%notfound; --exit 不能紧接 fetch 了,不然会漏记录       
20.    end loop;       
21.    close all_contacts_cur;       
22.end;     
  使用 fetch into 逐行获取游标数据
1.declare   
2. 
3.    --声明变量,参照字段的 type 来声明类型    
4.     v_id sr_contacts.sr_contact_id%type;    
5.     v_phone sr_contacts.contact_phone%type;    
6.     v_remark sr_contacts.remark%type;     
7. 
8.   cursor all_contacts_cur is select sr_contact_id ,contact_phone,remark from sr_contacts where rownum <= 100000;    
9. 
10.begin   
11.        
12.    open all_contacts_cur;    
13.    loop    
14.        fetch all_contacts_cur into v_id,v_phone,v_remark;    
15.        exit when all_contacts_cur%notfound;        
16.        --用 v_id/v_phone/v_remark 取出字段值来执行你的业务逻辑    
17.        null; --这里只放置一个空操作,只为测试循环取数的效率    
18.    end loop;    
19.    close all_contacts_cur;    
20.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.  type contacts_type is table of sr_contacts%rowtype;    
4.  v_contacts contacts_type;    
5.   cursor all_contacts_cur is --用 rownum 来限定取出的记录数来测试  
6.     select * from sr_contacts where rownum <= 10000;    
7. 
8.begin   
9.        
10.    open all_contacts_cur;    
11.    loop    
12.        fetch all_contacts_cur bulk collect into v_contacts limit 256;    
13.        for i in v_contacts.first .. v_contacts.last loop --遍历集合    
14.            --用 v_contacts(i).sr_contact_id/v_contacts(i).contact_phone/v_contacts(i).remark    
15.            --的形式来取出各字段值来执行你的业务逻辑      
16.        end loop;    
17.        exit when all_contacts_cur%notfound;    
18.    end loop;    
19.    close all_contacts_cur;    
20.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.    open all_contacts_cur;    
3.    fetch all_contacts_cur bulk collect into v_contacts;    
4.    for i in v_contacts.first .. v_contacts.last loop --遍历集合 
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.    end loop;    
10.    close all_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 批量效率的...通过使用 fetch bulk collect into,我们可以批量取游标中的数据,减少数据库的 I/O 次数,从而提高读取效率。这使得我们的数据库应用程序更加高效、更加稳定。

    批量插入数据到Oracle数据库Demo

    本教程将详细讲解如何进行批量插入数据到Oracle数据库,以提高数据导入的效率。 批量插入数据到Oracle数据库通常比单条插入更有效率,因为它减少了与数据库的交互次数,降低了网络延迟,并减少了系统资源的占用。...

    精准掌控数据流:MySQL游标在批量更新和删除中的应用

    ### MySQL 游标在批量更新和删除中的应用详解 #### 一、MySQL 游标概述 ...这种方法不仅可以增强代码的可读性和可维护性,还能提高数据库操作的效率。对于处理大量数据的应用程序来说,合理利用游标功能是非常重要的。

    PRO*C的批量读取

    为了提高效率,需要利用PRO*C提供的批量读取功能。 2. **批量读取设置**:通过设置`PREFETCH`选项来启用批量读取。例如,可以通过以下代码片段设置每次获取65535行记录: ```c EXECSQL DECLARE curSql CURSOR FOR...

    oracle 游标使用大全

    在实际开发中,游标可以提高开发效率、改善系统性能。 六、游标的优缺点 游标的优点是可以高效地访问和处理大量数据,提高系统性能。游标的缺点是需要占用系统资源,如果不正确地使用游标,可能会导致系统性能下降...

    jdbcTemplate分页彻底解决,使用游标滚动

    在数据库中,游标允许我们从结果集中顺序地读取一行数据,也可以向前或向后移动,甚至可以在当前行上进行修改。游标滚动对于处理大量数据尤其有用,因为它可以在不一次性加载所有数据到内存的情况下进行操作,从而...

    SQL游标原理和使用方法

    SQL游标是数据库管理系统中一个重要的概念,它允许程序员按...正确理解和运用游标,能够提高代码的灵活性和处理效率,解决许多特定场景下的问题。了解游标的工作原理和使用方法,对于任何数据库开发者都是十分必要的。

    SQL经典游标使用方法

    键集驱动游标在内存中保存一部分键,提高效率;只读/可写游标则决定了是否允许修改数据。 6. **游标示例**: 龙摆尾(坚行).sql.txt和游标实例.sql.txt很可能是包含具体游标使用示例的文件,可能涵盖了诸如迭代...

    Orcle游标实例.txt

    ### Oracle 游标实例解析及应用 #### 一、Oracle游标概述 在Oracle数据库中,游标(Cursor)是一种数据库对象,它允许程序能够逐行处理查询结果集中...正确使用游标不仅可以提高程序的效率,还可以增强程序的健壮性。

    第15章 游标的创建与使用.ppt

    3. 批量读取:为了提高效率,可以使用BULK COLLECT INTO语句批量获取多条数据,然后一次性存储到变量或集合中。 总之,游标是数据库编程中的重要工具,尤其在需要逐行处理数据时,游标提供了强大的控制力和灵活性,...

    学习基于SQL数据库的算法

    数据库中可以存储实体的数据集合,在进行运算时,数据库使用批量计算...与使用批量的方法比较,批量计算的方法消耗的资源相对比较少,而使用游标则占用太多的资源,速度比较慢,效率较低并且还有加锁条件等许多的限制。

    从文本批量读取数据到MySQL数据库并把名字也添加进去

    以下是一个关于如何从文本批量读取数据到MySQL数据库,并将文件名作为一列添加进去的详细步骤。 首先,确保你已经安装了MySQL数据库服务,并且有相应的数据库和表结构来存储这些数据。例如,你可以创建一个名为`...

    数据库游标:探索数据世界的导航仪

    2. **批量操作**:通过游标执行批量插入或更新操作,可以显著提高数据处理的效率。这对于需要频繁进行大批量数据操作的应用非常有用。 3. **存储过程调用**:使用游标调用数据库存储过程,可以实现更复杂的业务...

    不使用游标完成循环功能(下)

    对于那些希望提高查询效率的T-SQL程序员和数据库管理员而言,掌握不依赖游标的循环技术至关重要。本文将详细介绍两种不使用游标的循环方法,并阐述其背后的原理及应用场合。 #### 方法二:不带标识的临时表 **原理...

    批量创建数据库中所有表的触发器,删除所有触发器

    总的来说,批量创建和删除数据库中的触发器是一项有助于提高效率的任务,尤其当数据库表数量庞大时。通过熟练运用游标、动态SQL和系统视图,我们可以自动化这些过程,减轻数据库管理员的工作负担。但同时,也应理解...

    整理:oracle pl/sql 入门+ 数组使用+游标+动态SQL

    - 打开、关闭和迭代:使用OPEN、FETCH和CLOSE语句操作游标,通过循环读取查询结果。 - 使用游标变量:将游标结果赋值给变量,进行进一步的处理或计算。 4. 动态SQL: - 动态SQL的原理:在运行时构建和执行SQL...

    SQL SERVER 中游标的使用

    通过游标,我们可以实现复杂的数据处理操作,并提高数据处理的效率和灵活性。 以下是游标的优点: * 游标允许应用程序对查询语句 select 返回的行结果集中每一行进行相同或不同的操作,而不是一次对整个结果集进行...

    cursor游标

    游标允许我们逐行处理查询结果,而不是一次性加载所有数据,这对于大型数据集的处理非常有用,因为它可以减少内存消耗并提高性能。 在Oracle中,游标主要有以下几类: 1. **隐式游标**:每当执行一个DML(插入、...

    游标的应用

    游标允许开发者按照需要顺序地、交互式地访问数据,这在处理批量更新、分步处理或者需要多次读取同一数据集的情况下非常有用。 游标的类型主要包括四种:只进游标(FORWARD_ONLY)、静态游标(STATIC)、动态游标...

Global site tag (gtag.js) - Google Analytics