关闭

Oracle bulk 示例

标签: oracletablereturningnested引擎
7289人阅读 评论(0) 收藏 举报
分类:

 

 

之前整理了一篇blog,讲了一些bulk 的理论知识,参考:

            Oracle Bulk 与 性能优化 说明

            http://blog.csdn.net/tianlesoftware/article/details/6578351

 

在这篇,主要演示一下bulk的具体使用。 之前还整理了一篇bulk类似的一个优化,参考:

            Oracle 利用 rowid 提升 update 性能

            http://www.cndba.cn/Dave/article/1535

 

            Oracle有2个引擎来执行PL/SQL blocks 和 subprograms。那么在执行的时候,PL/SQL 引擎把DML 语句发送给SQL 引擎,然后由SQL 引擎执行,执行完毕后,SQL 引擎把结果集在发送给PL/SQL 引擎。

 

            与bulk 相关的有2个语法:forall 和 bulk collect。

            (1)FORALL. 将数据打包,一次性从PL/SQL 引擎发送给SQL 引擎。

            (2)bulk collect: 将处理之后的结果集放到bulk collect里,然后一次性把bulk collect从SQL 引擎发送给PL/SQL 引擎。           

 

            因为bulk 减少2个引擎之间的context switches,从而提升了SQL性能,当要处理的数据量越大,使用bulk 和不使用bulk 性能区别就越明显。

 

 

一.  Forall测试

1.1 delete 测试

1.1.1 使用for .. loop 循环

 

SYS@anqing2(rac2)> desc ta;      

 Name                        Null?    Type

 ------------------------------------------------- ----------------------------

 ID                                  NUMBER

 NAME                              VARCHAR2(10)

 

SYS@anqing2(rac2)> select count(*) fromta;

 

 COUNT(*)

----------

   999999

 

SYS@anqing2(rac2)> select * from tawhere rownum<5;

 

       ID NAME

---------- ----------

        1 dave

        1 dave

        1 dave

        1 dave

 

测试之前,先看下变量的声明:

TYPE IdList IS TABLE OF ta.id%TYPE;

TYPE ta_row IS TABLE OF ta%ROWTYPE INDEX BYPLS_INTEGER;
--声明变量

row_id_table  dbms_sql.Urowid_Table;

idl IdList;

rar ta_row;

--先定一个类型,然后声明该类型的变量,官网链接如下:

http://download.oracle.com/docs/cd/E11882_01/appdev.112/e10766/tdddg_subprograms.htm#TDDDG99950

 

/* Formatted on 2011/7/11 11:22:37(QP5 v5.163.1008.3004) */

DECLARE

   TYPEIdList IS TABLE OF ta.id%TYPE;

   idl   IdList;

BEGIN

   SELECT id BULK COLLECT INTO idl FROM ta WHERE ROWNUM <= 100000;

   FOR i IN idl.FIRST .. idl.LAST

   LOOP

      DELETE FROM ta WHERE id = idl (i);

   END LOOP;

END;

/

PL/SQL procedure successfully completed.

Elapsed: 00:00:33.59 -- 花了34秒

SYS@anqing2(rac2)> rollback;

Rollback complete.

Elapsed: 00:00:29.02 --rollback 花了29秒

 

 

1.1.2  forall

 

/* Formatted on 2011/7/11 11:25:59(QP5 v5.163.1008.3004) */

DECLARE

   TYPEIdList IS TABLE OF ta.id%TYPE;

   idl   IdList;

BEGIN

   SELECT id BULK COLLECT INTO idl FROM ta WHERE ROWNUM <= 100000;

   FORALL i IN idl.FIRST .. idl.LAST

      DELETE FROM ta WHERE id = idl(i);

END;

/

PL/SQL procedure successfully completed.

Elapsed: 00:00:29.45 --花了30秒,速度提升不是很多。才4秒

SYS@anqing2(rac2)> rollback;

Rollback complete.

Elapsed: 00:00:39.18

 

1.1.3 forall + rowid

 

/* Formatted on 2011/7/11 11:29:33(QP5 v5.163.1008.3004) */

DECLARE

  row_id_table   DBMS_SQL.Urowid_Table;

BEGIN

   SELECT ROWID BULK COLLECT INTOrow_id_table FROM ta WHERE ROWNUM <= 100000;

 

   FORALL i INrow_id_table.FIRST ..row_id_table.LAST

      DELETE FROM ta WHERE ROWID =row_id_table (i);

END;

/

PL/SQL procedure successfully completed.

Elapsed: 00:00:29.19  --比单纯的forall 块了仅1秒

SYS@anqing2(rac2)> rollback;

Rollback complete.

Elapsed: 00:00:31.08

 

注意一点:

            FORALL只能包含一条DML 语句,而FOR LOOP 可以包含多条

 

1.2 Insert

SYS@anqing2(rac2)>create table ta2 as select * from ta where 1=2;

Table created.

 

/* Formatted on 2011/7/11 14:31:11(QP5 v5.163.1008.3004) */

DECLARE

  TYPEta_row IS TABLE OF ta%ROWTYPEINDEX BY PLS_INTEGER;

  Trow   ta_row;

  t1     INTEGER;

  t2     INTEGER;

  t3     INTEGER;

BEGIN

  SELECT * BULK COLLECT INTO Trow FROM ta WHERE ROWNUM <= 100000;

  t1 := DBMS_UTILITY.get_time;

 

  FOR i IN 1 .. Trow.COUNT

  LOOP

     INSERT INTO ta2 VALUES trow (i);

  END LOOP;

 

  t2 := DBMS_UTILITY.get_time;

 

  FORALL i IN 1 .. Trow.COUNT

     INSERT INTO ta2 VALUES trow(i);

 

  t3 := DBMS_UTILITY.get_time;

 

  DBMS_OUTPUT.PUT_LINE ('ExecutionTime (secs)');

  DBMS_OUTPUT.PUT_LINE ('---------------------');

  DBMS_OUTPUT.PUT_LINE ('FORLOOP: ' || TO_CHAR ( (t2 - t1) / 100));

  DBMS_OUTPUT.PUT_LINE ('FORALL:' || TO_CHAR ( (t3 - t2) / 100));

  COMMIT;

END;

/

 

--结果如下:

Execution Time (secs)

---------------------

FOR LOOP: 24.52

FORALL: 2.91   -- 使用forall性能提升很明显

 

PL/SQL procedure successfully completed.

 

二. BULK COLLECT

2.1 SELECT INTO Statement with BULK COLLECT Clause

 

/* Formatted on 2011/7/11 15:25:28(QP5 v5.163.1008.3004) */

DECLARE

   TYPE idTabIS TABLE OF ta.id%TYPE;

 

   TYPEnameTab IS TABLE OF ta.name%TYPE;

   TYPE taSetIS TABLE OF ta%ROWTYPEINDEX BY PLS_INTEGER;

   tmp_ta     taSet;

   tmp_id     idTab;

   tmp_name   nameTab;

BEGIN

   SELECT id, name BULK COLLECT INTOtmp_id,tmp_name FROM ta WHERE ROWNUM < 5;

 

   SELECT id, name BULK COLLECT INTOtmp_ta FROM ta WHERE ROWNUM < 5;

 

   FOR i INtmp_ta.FIRST ..tmp_ta.LAST

   LOOP

      DBMS_OUTPUT.PUT_LINE(tmp_ta(i).id || ': ' ||tmp_ta (i).name);

   END LOOP;

 

   FOR j IN 1..tmp_id.count

   LOOP

      DBMS_OUTPUT.PUT_LINE(tmp_id(j) || '--' ||tmp_name (j));

   END LOOP;

END;

/

           

198854: dave

198855: dave

198856: dave

198857: dave

198854--dave

198855--dave

198856--dave

198857--dave

 

PL/SQL procedure successfully completed.

 

 

2.2 Row Limits for SELECT BULK COLLECT INTO Statements

            当Select bulk collect into 返回的结果集很大时,可以使用rownum 或者sample 来限制他的大小。 sample 表示总记录数的一个百分比。

 

DECLARE

TYPE idList IS TABLE OF ta.id%TYPE;

tmp_id idList;

tmp_id2 idList;

 

BEGIN

SELECT id BULK COLLECT INTO tmp_id FROM ta WHEREROWNUM <= 50;

SELECT id BULK COLLECT INTO tmp_id2 FROM taSAMPLE (1);

--这里采样为1%

DBMS_OUTPUT.PUT_LINE ('tmp_id count is: '||tmp_id.count);

DBMS_OUTPUT.PUT_LINE ('tmp_id2 count is:'|| tmp_id2.count);

END;

/

结果为:

tmp_id count is: 50

tmp_id2 count is: 11044

 

PL/SQL procedure successfully completed.

 

2.3 FETCH Statement with BULK COLLECT Clause

            FETCHBULK COLLECT  可以将整个结果集返回到一个或者多个collectionvariables。

 

2.3.1  Bulk-Fetching into Two Nested Tables

DECLARE

TYPE nameList IS TABLE OF ta. name%TYPE;

TYPE idList IS TABLE OF ta.id%TYPE;

CURSOR c1 IS SELECT id,name FROM ta WHERE rownum<20;

TYPE RecList IS TABLE OF c1%ROWTYPE;

 

tmp_name NameList;

tmp_id idList;

recs RecList;

v_limit PLS_INTEGER := 2;

 

PROCEDURE print_results IS

BEGIN

-- Check if collections are empty:

IF tmp_name IS NULL ORtmp_name.COUNT = 0 THEN

DBMS_OUTPUT.PUT_LINE('Noresults!');

ELSE

DBMS_OUTPUT.PUT_LINE('Result:');

FOR i INtmp_name.FIRST ..tmp_name.LAST LOOP

DBMS_OUTPUT.PUT_LINE(' name:' ||tmp_name(i) || ': id' ||tmp_id(i));

END LOOP;

END IF;

END;

 

BEGIN

DBMS_OUTPUT.PUT_LINE ('---Processing all results simultaneously ---');

OPEN c1;

FETCH c1 BULK COLLECT INTOtmp_id,tmp_name;

CLOSE c1;

print_results();

 

DBMS_OUTPUT.PUT_LINE ('---Processing ' ||v_limit || ' rowsat a time ---');

OPEN c1;

LOOP

FETCH c1 BULK COLLECT INTO tmp_id,tmp_nameLIMITv_limit;

EXIT WHENtmp_name.COUNT = 0;

print_results();

END LOOP;

CLOSE c1;

--这种情况下,每次会处理limit的个数,知道处理完

 

DBMS_OUTPUT.PUT_LINE ('---Fetching records rather than columns ---');

OPEN c1;

FETCH c1 BULK COLLECT INTO recs;

FOR i IN recs.FIRST .. recs.LAST

LOOP

-- Now all columns from result setcome from one record

DBMS_OUTPUT.PUT_LINE (' Nameis: ' ||recs(i).name || ': id' ||recs(i).id);

END LOOP;

END;

/

--- Processing all results simultaneously---

Result:

name: dave: id198854

name: dave: id198855

name: dave: id198856

name: dave: id198857

name: dave: id198858

name: dave: id198859

name: dave: id198860

name: dave: id198861

name: dave: id198862

name: dave: id198863

name: dave: id198864

name: dave: id198865

name: dave: id198866

name: dave: id198867

name: dave: id198868

name: dave: id198869

name: dave: id198870

name: dave: id198871

name: dave: id198872

--- Processing 2 rows at a time ---

Result:

name: dave: id198854

name: dave: id198855

Result:

name: dave: id198856

name: dave: id198857

Result:

name: dave: id198858

name: dave: id198859

Result:

name: dave: id198860

name: dave: id198861

Result:

name: dave: id198862

name: dave: id198863

Result:

name: dave: id198864

name: dave: id198865

Result:

name: dave: id198866

name: dave: id198867

Result:

name: dave: id198868

name: dave: id198869

Result:

name: dave: id198870

name: dave: id198871

Result:

name: dave: id198872

--- Fetching records rather than columns---

Name is: dave: id198854

Name is: dave: id198855

Name is: dave: id198856

Name is: dave: id198857

Name is: dave: id198858

Name is: dave: id198859

Name is: dave: id198860

Name is: dave: id198861

Name is: dave: id198862

Name is: dave: id198863

Name is: dave: id198864

Name is: dave: id198865

Name is: dave: id198866

Name is: dave: id198867

Name is: dave: id198868

Name is: dave: id198869

Name is: dave: id198870

Name is: dave: id198871

Name is: dave: id198872

 

PL/SQL procedure successfully completed.

 

2.3.2  Bulk-Fetchinginto Nested Table of Records

DECLARE

CURSOR c1 IS SELECT id, name FROM ta;

TYPE NameSet IS TABLE OF c1%ROWTYPE;

tmp_ta NameSet; -- nested table of records

TYPE cursor_var_type is REF CURSOR;

cv cursor_var_type;

BEGIN

-- Assign values to nested table ofrecords:

OPEN cv FOR

SELECT id,name FROM ta WHERE rownum<10;

FETCH cv BULK COLLECT INTO tmp_ta;

CLOSE cv;

-- Print nested table of records:

FOR i IN tmp_ta.FIRST .. tmp_ta.LAST LOOP

DBMS_OUTPUT.PUT_LINE (tmp_ta(i).id || ' '||tmp_ta (i). name);

END LOOP;

END;

/

结果:

198854 dave

198855 dave

198856 dave

198857 dave

198858 dave

198859 dave

198860 dave

198861 dave

198862 dave

PL/SQL procedure successfully completed.

 

 

2.4 Row Limits for FETCH BULK COLLECT Statements

            在 FETCH BULK COLLECT 中使用limit 控制每个collection 的大小。 如果返回集很大,就分成多个collection 来处理。 这个用法在2.3节中有使用。

 

DECLARE

TYPE numtab IS TABLE OF NUMBER INDEX BYPLS_INTEGER;

CURSOR c1 IS SELECT id FROM ta WHERE rownum<10;

empids numtab;

BEGIN

OPEN c1;

LOOP -- Fetch 10 rows or fewer in eachiteration

FETCH c1 BULK COLLECT INTO empids LIMIT 2;

EXIT WHEN empids.COUNT =0; -- Not: EXIT WHEN c1%NOTFOUND

 

DBMS_OUTPUT.PUT_LINE ('------- Results fromOne Bulk Fetch --------');

FOR i IN 1..empids.COUNT LOOP

DBMS_OUTPUT.PUT_LINE ('Employee Id: ' ||empids(i));

END LOOP;

END LOOP;

CLOSE c1;

END;

/

 

结果如下:

------- Results from One Bulk Fetch--------

Employee Id: 198854

Employee Id: 198855

------- Results from One Bulk Fetch--------

Employee Id: 198856

Employee Id: 198857

------- Results from One Bulk Fetch--------

Employee Id: 198858

Employee Id: 198859

------- Results from One Bulk Fetch--------

Employee Id: 198860

Employee Id: 198861

------- Results from One Bulk Fetch--------

Employee Id: 198862

 

PL/SQL procedure successfully completed.

 

2.5 RETURNING INTO Clause with BULK COLLECT Clause

            RETURNINGBULK COLLECT INTO 可以显示 INSERT, UPDATE, DELETE, or EXECUTE IMMEDIATE 的具体数据.

 

DECLARE

TYPE idList IS TABLE OF ta.id %TYPE;

tmp_id idList;

TYPE NameList IS TABLE OF ta.name%TYPE;

tmp_name NameList;

BEGIN

DELETE FROM ta WHERE rownum<10

RETURNING id,name BULK COLLECT INTOtmp_id,tmp_name;

 

DBMS_OUTPUT.PUT_LINE ('Deleted ' ||SQL%ROWCOUNT || ' rows:');

FOR i IN tmp_id.FIRST.. tmp_id.LAST

LOOP

DBMS_OUTPUT.PUT_LINE ('ID #' || tmp_id(i)|| ': ' || tmp_name(i));

END LOOP;

END;

/

返回结果:

Deleted 9 rows:

ID #198854: dave

ID #198855: dave

ID #198856: dave

ID #198857: dave

ID #198858: dave

ID #198859: dave

ID #198860: dave

ID #198861: dave

ID #198862: dave

 

PL/SQL procedure successfully completed.

 

2.6 Using FORALL Statement and BULK COLLECT Clause Together

2.6.1 示例1

DECLARE

TYPE idList IS TABLE OF ta.id%TYPE;

TYPE nameList IS TABLE OF ta.name%TYPE;

 

tmp_id idList;

tmp_id2 idList;

tmp_name nameList;

TYPE cursor_var_type is REF CURSOR;

cv cursor_var_type;

 

BEGIN

OPEN cv FOR SELECT id FROM ta WHERE rownum<10;

FETCH cv BULK COLLECT INTOtmp_id2;

CLOSE cv;

 

FORALL i IN 1..tmp_id2.count

DELETE FROM ta WHERE id=tmp_id2(i) RETURNING id,name BULK COLLECT INTOtmp_id,tmp_name;

 

DBMS_OUTPUT.PUT_LINE ('Deleted' || SQL%ROWCOUNT || ' rows:');

FOR i INtmp_id.FIRST ..tmp_id.LAST

LOOP

DBMS_OUTPUT.PUT_LINE ('Id #' ||tmp_id (i) || ' Name#' ||tmp_name (i));

END LOOP;

END;

/

 

Deleted 27 rows:

Id #198863 Name #dave

Id #198863 Name #dave

Id #198863 Name #dave

Id #198864 Name #dave

...

Id #198871 Name #dave

Id #198871 Name #dave

Id #198871 Name #dave

 

PL/SQL procedure successfully completed.

 

三. 用bulk 进行大表update 的测试

            在blog:

                        Oracle 利用 rowid 提升 update 性能

                        http://www.cndba.cn/Dave/article/1535

            用rowid 进行了性能提示,这里测试下,看看使用bulk,性能能提升多少。在这里我们使用forall+bulk collect+ rowid 进行测试。

 

SQL> select count(*) from dba;

 

 COUNT(*)

----------

  3080070

 

SQL> select count(*) from dave;

 

 COUNT(*)

----------

  3080070

 

两张表都是300w。 在使用rowid 时,全表更新用了近5分钟。

 

/* Formatted on 2011/7/11 23:19:03(QP5 v5.163.1008.3004) */

DECLARE

   maxrows        NUMBER DEFAULT 45000;

   row_id_table   DBMS_SQL.Urowid_Table;

   TYPE acList IS TABLE OF dba.area_code%TYPE;

   tmp_ac         acList;

 

   CURSOR c1 IS

        SELECT a.area_code, b.ROWID row_id FROM dave a, dba b

         WHERE a.id = b.id ORDER BY b.ROWID;

BEGIN

   OPEN c1;

   LOOP

      FETCH c1 BULK COLLECT INTO tmp_ac, row_id_table LIMIT maxrows;

             EXIT WHEN row_id_table.COUNT = 0;

 

      FORALL i IN 1 .. row_id_table.COUNT

         UPDATE dba SET prov_code = tmp_ac(i) WHERE ROWID = row_id_table(i);

 

      COMMIT;

      END LOOP;

            CLOSE c1;

END;

/

 

用limit 为1000 测试,用了Elapsed: 00:06:37.34

用limit 为40000 测试,用了Elapsed: 00:04:46.94

用limit 为45000 测试,用了Elapsed: 00:04:22.82

用limit 为50000 测试,用了Elapsed: 00:04:31.09

用limit 为50000 测试,用了Elapsed: 00:04:36.12

用limit 为100000 测试,用了Elapsed: 00:09:41.22

 

delete 和 insert 的相关语法和上面的类似,相关部分改成如下:

forall i in 1 .. row_id_table.count
insert into tb select * from ta where rowid = row_id_table(i) ;


forall i in 1 .. row_id_table.count
delete from ta where rowid = row_id_table(i) ;

 

 

 

 

-------------------------------------------------------------------------------------------------------

QQ:492913789

Email:ahdba@qq.com

Blog: http://www.cndba.cn/dave


DBA1 群:62697716(满);   DBA2 群:62697977(满)  DBA3 群:62697850(满)  

DBA 超级群:63306533(满);  DBA4 群: 83829929  DBA5群: 142216823   

DBA6 群:158654907  聊天 群:40132017   聊天2群:69087192

--加群需要在备注说明Oracle表空间和数据文件的关系,否则拒绝申请

0
0
查看评论

oracle学习之bulk collect用法

转自:http://www.cnblogs.com/Ronger/archive/2011/09/22/2185574.html 通过bulk collect减少loop处理的开销,使用Bulk Collect提高Oracle查询效率 Oracle8i中首次引入了Bulk Collect特性...
  • Leeboy_Wang
  • Leeboy_Wang
  • 2012-09-18 11:47
  • 20004

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

fetch bulk collect into 的使用格式是:fetch some_cursor collect into col1, col2 limit xxx。col1、col2 是声明的集合类型变量,xxx 为每次取数据块的大小(记录数),相当于缓冲区的大小,可以不指定 limit xxx ...
  • txj8859_1
  • txj8859_1
  • 2015-11-22 21:57
  • 2716

Oracle数据库的批量操作,forall,BULK COLLECT

oracle forall FORALL语句的一个关键性改进,它可大大简化代码,并且对于那些要在PL/SQL程序中更新很多行数据的程序来说,它可显著提高其性能。 1: 用FORALL来增强DML的处理能力 Oracle为Oracle8i中的PL/SQL引入了两个新的数据操纵语言(DML)语句...
  • jie1336950707
  • jie1336950707
  • 2015-11-21 18:54
  • 759

oracle中bulk collect into用法

通过bulk collect减少loop处理的开销 采用bulk collect可以将查询结果一次性地加载到collections中。 而不是通过cursor一条一条地处理。 可以在select into,fetch into,returning into语句使用bulk collect。 ...
  • whhitgen
  • whhitgen
  • 2013-10-09 16:03
  • 9786

Oracle 之 bulk collect 的用法

通过bulk collect减少loop处理的开销采用bulk collect可以将查询结果一次性地加载到collections中。而不是通过cursor一条一条地处理。可以在select into,fetch into,returning into语句使用bulk collect。注意在使用bul...
  • acoolboy1984
  • acoolboy1984
  • 2007-08-31 17:40
  • 1830

oracle批量处理(bulk collect)

  • 2012-03-19 09:37
  • 54KB
  • 下载

oracle使用bulk collect insert实现大数据快速迁移

在Oracle 数据库中,将一张表的部分字段转换导入到另外一张表的部分字段中,如"insert into t_target(owner,object_name) from t_source;" 。    这也是解决此类数据迁移的的常规操作方法。 ...
  • mh942408056
  • mh942408056
  • 2014-03-30 23:07
  • 1484

ORACLE Bulk Binding批量绑定

 批量绑定(Bulk Binding)通过一次绑定一个完整的集合,然后在PL/SQL块内部按照数组进行数据处理来加快SQL语句的效率。通过两个DML语句:Bulk Collect和ForAll来实现。Bulk Collect—用来提高查询(select)的性能ForAll—用来提高(Ins...
  • huanghui22
  • huanghui22
  • 2006-10-07 16:56
  • 5797

Oracle 集合 bulk collect 例子

DECLARE TYPE t_emp IS TABLE OF hr.employees%ROWTYPE; l_emp t_emp := t_emp(); BEGIN SELECT * bulk COLLECT INTO l_emp FROM hr.employees WHERE rownum ...
  • maoliangfei2011
  • maoliangfei2011
  • 2014-01-20 22:10
  • 490

Oracle bulk 示例

之前整理了一篇blog,讲了一些bulk 的理论知识,参考:            Oracle Bulk 与 性能优化 说明     &#...
  • tianlesoftware
  • tianlesoftware
  • 2011-07-12 00:51
  • 7289
    Oracle数据库问题解决方案和故障排除手册
    Dave 新博客
    CNDBA 社区交流QQ群

    • CNDBA_1: 62697850 (大量空闲)
    • CNDBA_2: 62697716(大量空闲)
    • CNDBA_3: 283816689
    • CNDBA_4: 391125754
    • CNDBA_5: 104207940(满)
    • CNDBA_6: 62697977
    • CNDBA_7: 142216823(满)
    个人资料
    • 访问:12445823次
    • 积分:118813
    • 等级:
    • 排名:第4名
    • 原创:1006篇
    • 转载:86篇
    • 译文:2篇
    • 评论:1576条
    Oracle 组织

    安徽DBA俱乐部
    • 安徽DBA俱乐部,俱乐部整合安徽地区的IT资源,现有成员已经包含安徽大部分IT公司,俱乐部除了资讯信息分享之外,也会定期举行线下活动。欢迎安徽地区的DBA 加入.

      QQ群:363076776 安徽DBA俱乐部/ahdba.com ,备注,加群必须注明籍贯,该群只对安徽地区开放。

    • 网址:www.ahdba.com
    最新评论