Oracle select into,fetch into,returning into, bulk collect into limit, forall in 批量效率的读取游标数据提高性能

末蓝、 2022-04-10 05:11 755阅读 0赞

# [ORACLE FETCH BULK COLLECT INTO LIMIT][] #

![复制代码][20181227170343626]

DECLARE
      TYPE rr IS REF CURSOR;
      TYPE r_emp IS RECORD(
        empno    NUMBER(4),
        ename    VARCHAR2(10),
        job      VARCHAR2(9),
        mgr      NUMBER(4),
        hiredate DATE,
        sal      NUMBER(7, 2),
        comm     NUMBER(7, 2),
        deptno   NUMBER(2));
      TYPE t_e IS TABLE OF r_emp;
      e t_e;
      ee t_e:= t_e();
      r rr;
    BEGIN
      OPEN r FOR
        SELECT * FROM apps.emp;
      LOOP
        EXIT WHEN r%notfound;--写在FETCH后面会掉数据
        FETCH r BULK COLLECT
          INTO e LIMIT 5;
        ee := ee multiset union e;
      END LOOP;
      for i in 1..ee.count loop
        dbms_output.put_line(ee(i).empno);
      end loop;
    END;

![复制代码][20181227170343626]

分类: [ORACLE][]

好文要顶 关注我 收藏该文 ![20181227170343717][] ![20181227170343773][]

[![20181227170343820][]][20181227170343820 1]

[simplelil][20181227170343820 1]  
[关注 - 4][- 4]  
[粉丝 - 2][- 2]

\+加关注

[« ][Link 7]上一篇：[ORACLE rowid切分大表][Link 7]  
[» ][Link 8]下一篇：[ORACLE object\_id和data\_object\_id][Link 8]

发表于 2016-07-20 16:46 [SIMPLELIL][Link 2] 阅读(147) 评论(0) [编辑][Link 9] [收藏][Link 10]

[https://www.cnblogs.com/wolil/p/5688929.html][ORACLE FETCH BULK COLLECT INTO LIMIT]

# oracle中bulk collect into用法 #

2013年10月09日 16:03:17 [王景远][Link 11] 阅读数：11780

1.  `通过bulk collect减少loop处理的开销`
2.   
3.  `采用bulk collect可以将查询结果一次性地加载到collections中。`
4.  `而不是通过cursor一条一条地处理。`
5.  `可以在select into,fetch into,returning into语句使用bulk collect。`
6.  `注意在使用bulk collect时，所有的into变量都必须是collections.`
7.   
8.   
9.  `举几个简单的例子：`
10. `--在select into语句中使用bulk collect`
11. `DECLARE`
12. `TYPE SalList IS TABLE OF emp.sal%TYPE;`
13. `sals SalList;`
14. `BEGIN`
15. `-- Limit the number of rows to 100.`
16. `SELECT sal BULK COLLECT INTO sals FROM emp`
17. `WHERE ROWNUM <= 100;`
18. `-- Retrieve 10% (approximately) of the rows in the table.`
19. `SELECT sal BULK COLLECT INTO sals FROM emp SAMPLE 10;`
20. `END;`
21. `/`
22. `--在fetch into中使用bulk collect`
23. `DECLARE`
24. `TYPE DeptRecTab IS TABLE OF dept%ROWTYPE;`
25. `dept_recs DeptRecTab;`
26. `CURSOR c1 IS`
27. `SELECT deptno, dname, loc FROM dept WHERE deptno > 10;`
28. `BEGIN`
29. `OPEN c1;`
30. `FETCH c1 BULK COLLECT INTO dept_recs;`
31. `END;`
32. `/`
33. `--在returning into中使用bulk collect`
34. `CREATE TABLE emp2 AS SELECT * FROM employees;`
35. `DECLARE`
36. `TYPE NumList IS TABLE OF employees.employee_id%TYPE;`
37. `enums NumList;`
38. `TYPE NameList IS TABLE OF employees.last_name%TYPE;`
39. `names NameList;`
40. `BEGIN`
41. `DELETE FROM emp2 WHERE department_id = 30`
42. `RETURNING employee_id, last_name BULK COLLECT INTO enums, names;`
43. `dbms_output.put_line('Deleted ' || SQL%ROWCOUNT || ' rows:');`
44. `FOR i IN enums.FIRST .. enums.LAST`
45. `LOOP`
46. `dbms_output.put_line('Employee #' || enums(i) || ': ' || names(i));`
47. `END LOOP;`
48. `END;`
49. `/`
50. `DROP TABLE emp2;`

[http://xuejiangtao.iteye.com/blog/1168570][http_xuejiangtao.iteye.com_blog_1168570]

\----------------------------

如果有个大数据量的DML操作事务,在OLAP报表等低并发库里. 并且强制归档模式中.

采用BULK 和FORALL 会比较快!

1.  `open cur_COLUMN_USER;`
2.  `loop`
3.  `fetch cur_COLUMN_USER bulk collect`
4.  `into`
5.  `l_ARY_statedate,`
6.  `l_ARY_form,`
7.  `l_ARY_columnid,`
8.  `l_ARY_usernumber,`
9.  `l_ARY_new_user,`
10. `l_ARY_exit_use`
11. `limit g_batch_size_n;`
12.  
13. `forall i in 1..l_ARY_statedate.count`
14. `insert into content_lst_day`
15. `(......)`
16. `values(l_ary_statedate(i),....);`
17.  
18. `commit;`
19. `end loop;`
20. `相对使用普通游标循环提取数据出来处理的话 会快很多.`

原因 1 bulk collect into 到数组 可以一次型把数据提取出来,减少了循环当中PL/SQL和SQL引撑的切换时间

原因 2 forall in ..... 也是一次型提交数据到某个地方 也同样减少了循环当中PL/SQL和SQL引撑的切换时间

注意 1 数据太大 要设置合理的LIMIT 否则提取到数组的数据会爆了PGA的回话内存

原因 3 bulk 内部操作 对insert delete 做了优化 采用批量方法.极大减少了redo 和Undo 使用量.

原因 4 为证明 当一个大数据量的insert 会超级慢,如果分批插入的总时间 比一次插入省很多时间.

An ORA-22813 when using BULK COLLECT is typically expected behavior indicating that you have exceeded the amount of free memory in the PGA. As collections are processed by PL/SQL they use the PGA to store their memory structures. Depending on the LIMIT size of the BULK COLLECT and additional processing of the collected data you may exceed the free memory of the PGA. While intuitively you may think that increasing the PGA memory and increasing the LIMIT size will increase performance, the following example shows you that this is not true in this case. So, by reviewing this example you should be able to strike a balance between a reasonable LIMIT size and the size of the PGA while maintaining a high level of performance using BULK COLLECT.

[https://blog.csdn.net/whhitgen/article/details/12511395][https_blog.csdn.net_whhitgen_article_details_12511395]

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

2015年11月22日 21:57:55 [憬筱][Link 12] 阅读数：4969更多

个人分类： [oracle][]

fetch bulk collect into 的使用格式是：fetch some\_cursor collect into col1, col2 limit xxx。col1、col2 是声明的集合类型变量，xxx 为每次取数据块的大小（记录数），相当于缓冲区的大小，可以不指定 limit xxx 大小。下面以实际的例子来说明它的使用，并与逐条取记录的 fetch into 执行效率上进行比较。  
**使用 fetch bulk collect into 获取游标数据**

`01.``declare`

`02.``   `

`03.``  ``--声明需要集合类型及变量，参照字段的 type 来声明类型   `

`04.``  ``type id_type ``is` `table` `of` `sr_contacts.sr_contact_id%type;   `

`05.``  ``v_id id_type;   `

`06.``      `

`07.``  ``type phone_type ``is` `table` `of` `sr_contacts.contact_phone%type;   `

`08.``  ``v_phone phone_type;   `

`09.``      `

`10.``  ``type remark_type ``is` `table` `of` `sr_contacts.remark%type;   `

`11.``  ``v_remark remark_type;   `

`12.``   `

`13.``  ``cursor` `all_contacts_cur ``is` `--用 rownum 来限定取出的记录数来测试   `

`14.``     ``select` `sr_contact_id,contact_phone,remark ``from` `sr_contacts ``where` `rownum <= 100000;   `

`15.``   `

`16.``begin`

`17.``        `

`18.``    ``open` `all_contacts_cur;   `

`19.``    ``loop   `

`20.``        ``fetch` `all_contacts_cur bulk collect ``into` `v_id,v_phone,v_remark limit 256;   `

`21.``        ``for` `i ``in` `1..v_id.``count` `loop ``--遍历集合   `

`22.``            ``--用 v_id(i)/v_phone(i)/v_remark(i) 取出字段值来执行你的业务逻辑   `

`23.``            ``null``; ``--这里只放置一个空操作，只为测试循环取数的效率  `

`24.``        ``end` `loop;   `

`25.``        ``exit ``when` `all_contacts_cur%notfound; ``--exit 不能紧接 fetch 了，不然会漏记录   `

`26.``    ``end` `loop;   `

`27.``    ``close` `all_contacts_cur;   `

`28.``end``;`

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

`01.``declare`

`02.`` `

`03.``  ``--声明变量，参照字段的 type 来声明类型`

`04.``  ``v_id sr_contacts.sr_contact_id%type;`

`05.``  ``v_phone sr_contacts.contact_phone%type;`

`06.``  ``v_remark sr_contacts.remark%type;`

`07.`` `

`08.``  ``cursor` `all_contacts_cur ``is`  `--用 rownum 来限定取出的记录数来测试`

`09.``     ``select` `sr_contact_id,contact_phone,remark ``from` `sr_contacts ``where` `rownum <= 100000;`

`10.`` `

`11.``begin`

`12.``     `

`13.``    ``open` `all_contacts_cur;`

`14.``    ``loop`

`15.``        ``fetch` `all_contacts_cur ``into` `v_id,v_phone,v_remark;`

`16.``        ``exit ``when` `all_contacts_cur%notfound;    `

`17.``        ``--用 v_id/v_phone/v_remark 取出字段值来执行你的业务逻辑`

`18.``        ``null``; ``--这里只放置一个空操作，只为测试循环取数的效率`

`19.``    ``end` `loop;`

`20.``    ``close` `all_contacts_cur;`

`21.``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  属性来进行遍历。

`01.``declare`

`02.`` `

`03.``  ``--声明需要集合类型及变量，参照字段的 type 来声明类型`

`04.``  ``type contacts_type ``is` `table` `of` `sr_contacts%rowtype;`

`05.``  ``v_contacts contacts_type;`

`06.``   `

`07.``  ``cursor` `all_contacts_cur ``is` `--用 rownum 来限定取出的记录数来测试`

`08.``     ``select` `* ``from` `sr_contacts ``where` `rownum <= 10000;`

`09.`` `

`10.``begin`

`11.``     `

`12.``    ``open` `all_contacts_cur;`

`13.``    ``loop`

`14.``        ``fetch` `all_contacts_cur bulk collect ``into` `v_contacts limit 256;`

`15.``        ``for` `i ``in` `v_contacts.``first` `.. v_contacts.``last` `loop ``--遍历集合`

`16.``            ``--用 v_contacts(i).sr_contact_id/v_contacts(i).contact_phone/v_contacts(i).remark`

`17.``            ``--的形式来取出各字段值来执行你的业务逻辑`

`18.``            ``null``; ``--这里只放置一个空操作，只为测试循环取数的效率`

`19.``        ``end` `loop;`

`20.``        ``exit ``when` `all_contacts_cur%notfound;`

`21.``    ``end` `loop;`

`22.``    ``close` `all_contacts_cur;`

`23.``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 部分可写成：

`01.``begin`

`02.``    ``open` `all_contacts_cur;`

`03.``    ``fetch` `all_contacts_cur bulk collect ``into` `v_contacts;`

`04.``    ``for` `i ``in` `v_contacts.``first` `.. v_contacts.``last` `loop ``--遍历集合`

`05.``        ``--用 v_contacts(i).sr_contact_id/v_contacts(i).contact_phone/v_contacts(i).remark`

`06.``        ``--的形式来取出各字段值来执行你的业务逻辑`

`07.``        ``null``; ``--这里只放置一个空操作，只为测试循环取数的效率`

`08.``        ``dbms_output.put_line(2000);`

`09.``    ``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 操作

[https://blog.csdn.net/txj8859\_1/article/details/49982037][https_blog.csdn.net_txj8859_1_article_details_49982037]

[Link 1]: https://www.cnblogs.com/
[Link 2]: https://www.cnblogs.com/wolil/
[Link 3]: /images/20220402/af551918f1d54d18b7cf3ed9684f50cc.pngEditPosts.aspx?opt=1
[Link 4]: https://msg.cnblogs.com/send/simplelil
[Link 5]: https://www.cnblogs.com/wolil/rss
[20181227170343585]: https://img-blog.csdnimg.cn/20181227170343585
[Link 6]: /images/20220402/af551918f1d54d18b7cf3ed9684f50cc.png
[ORACLE FETCH BULK COLLECT INTO LIMIT]: https://www.cnblogs.com/wolil/p/5688929.html
[20181227170343626]: /images/20220402/bfc906e4b3d544ddb55fe06f576c32fa.png
[ORACLE]: https://www.cnblogs.com/wolil/category/834550.html
[20181227170343717]: https://img-blog.csdnimg.cn/20181227170343717
[20181227170343773]: /images/20220402/e4600ff6a5ce49b986062606a0b4a7de.png
[20181227170343820]: /images/20220402/0ef0a44f34fc452faf5fce3c83b1b47d.png
[20181227170343820 1]: http://home.cnblogs.com/u/wolil/
[- 4]: http://home.cnblogs.com/u/wolil/followees
[- 2]: http://home.cnblogs.com/u/wolil/followers
[Link 7]: https://www.cnblogs.com/wolil/p/5685626.html
[Link 8]: https://www.cnblogs.com/wolil/p/5691048.html
[Link 9]: /images/20220402/af551918f1d54d18b7cf3ed9684f50cc.pngEditPosts.aspx?postid=5688929
[Link 10]: https://www.cnblogs.com/wolil/p/5688929.html#
[Link 11]: https://me.csdn.net/whhitgen
[http_xuejiangtao.iteye.com_blog_1168570]: http://xuejiangtao.iteye.com/blog/1168570
[https_blog.csdn.net_whhitgen_article_details_12511395]: https://blog.csdn.net/whhitgen/article/details/12511395
[Link 12]: https://me.csdn.net/txj8859_1
[oracle]: https://blog.csdn.net/txj8859_1/article/category/5587673
[https_blog.csdn.net_txj8859_1_article_details_49982037]: https://blog.csdn.net/txj8859_1/article/details/49982037