之前整理了一篇blog，讲了一些bulk 的理论知识，参考：
Oracle
Bulk 与 性能优化 说明
http://blog.csdn.net/xujinyang/article/details/6823244
在这篇，主要演示一下bulk的具体使用。 之前还整理了一篇bulk类似的一个优化，参考：
Oracle
利用 rowid 提升 update 性能
http://blog.csdn.net/xujinyang/article/details/6823248
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_tableDBMS_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://blog.csdn.net/tianlesoftware/article/details/6576156
用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_tableDBMS_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) ;
-------------------------------------------------------------------------------------------------------
分享到：
相关推荐
根据提供的文件信息，我们可以深入探讨Oracle中的`BULK COLLECT`功能及其在实际场景中的应用。这段代码示例虽然较为杂乱，但可以提炼出的关键点主要包括：如何使用`BULK COLLECT`来提高查询效率、如何声明和使用表...
### Oracle批量处理(Bulk Collect) #### 一、概述 在Oracle数据库中，`BULK COLLECT` 是一种高效的数据检索机制，它允许一次性从数据库中提取多行数据，并将其存储在一个PL/SQL集合中。与传统的游标逐行处理相比，...
在给定的示例代码中，展示了如何使用BULK COLLECT来批量收集数据并进行操作。 首先，定义了两个表类型（`t_table` 和 `t_pg3`），其中 `t_table` 是一个索引表，存储VARCHAR2类型的值，通过VARCHAR2类型的键进行...
本示例将详细讲解如何在Oracle中使用存储过程实现分页查询。 首先，我们需要理解分页的基本概念。分页通常涉及两个关键参数：当前页码（Page Number）和每页显示的记录数（Limit）。通过这两个参数，我们可以计算出...
在Oracle数据库中，Bulk Collect是一个非常实用的特性，它允许我们一次从游标（Cursor）中批量地获取多行数据，极大地提高了处理大量数据时的性能。本文将深入讲解如何在PL/SQL中使用Bulk Collect实现Cursor的批量...
本文将基于提供的示例代码，深入探讨Oracle中用于快速查询和更新大数据的一些关键技术点。 #### 一、批量处理技术：`BULK COLLECT` 批量处理是提高数据处理效率的重要手段之一。通过一次性获取多条记录，可以显著...
* 可变数组可以使用 `bulk collect` 语句来快速地从表中检索数据。 * 多维数组可以用于存储多个记录，并可以使用索引来访问数组中的元素。 Oracle 数组是一种功能强大且灵活的数据类型，可以用来存储大量的数据，...
首先，C#通常使用ADO.NET（数据访问对象）框架来与数据库交互，包括SQL Server、MySQL、Oracle等。ADO.NET提供DataSet、DataTable等组件，可以用来存储大量数据，并支持批量插入、更新和删除操作。例如，通过...
5. **批量操作**：如文章中提供的示例所示，批量处理操作（如BULK COLLECT）可能会导致较大的内存消耗。这是因为这类操作会在内存中缓存大量的数据，如果没有适当的限制（如LIMIT），很容易导致内存不足的问题。 ##...
UTL_FILE.PUT_RAW(file, DBMS_LOB.GETBULK(lob_loc, 32767), TRUE); DBMS_LOB.FILECLOSE(lob_loc); UTL_FILE.FCLOSE(file); END; / ``` 这个过程会将数据库中的图片数据写入到`YOUR_DIR`目录下的`temp_image....
在 Oracle 数据库中，Bulk Collect 是一个非常重要的特性，它主要用在 PL/SQL 代码中，用于提高数据处理的效率。Bulk Collect 允许一次性地将查询结果集批量加载到一个集合（collections）中，而不是传统的逐行处理...
Oracle数据库提供了一种高效的方法来处理批量操作，如查询、删除和更新，这就是BULK COLLECT和数组集合类型的使用。BULK COLLECT是PL/SQL中的一个关键字，它允许我们一次性收集多行数据，而不是逐行处理，从而显著...
不过需要注意的是，这种方法依赖于本地安装的Office组件，如果在服务器环境或者无桌面环境运行，可能需要寻找其他解决方案，如使用开源的库如EPPlus来处理Excel，或者将Excel数据转换为CSV，再通过Oracle的BULK ...
总结来说，本示例展示了如何在Oracle存储过程中定义和使用结构化数组，以及如何在Java中调用这些存储过程并解析返回的多行多列数据。通过这种方式，你可以高效地处理复杂的数据结构，同时利用Java的灵活性来处理这些...
通过上述示例代码，我们可以看到如何利用Oracle的游标、BULK COLLECT 和 FOR ALL 结合ROWID来实现高效的大批量数据删除。这种方法不仅大大提高了数据处理的速度，还保证了操作的安全性和可靠性。对于经常需要处理...
这个文档很可能包含详细的步骤指导，如何在Oracle环境中配置和使用Java进行存储过程开发，可能包括示例代码、最佳实践和常见问题解答。 8. **文件"www.pudn.com.txt"** 这个文本文件可能是从PUDN网站上摘录的相关...
文档"将excel导入oracle.doc"应该包含了更详尽的步骤指导和代码示例，可以帮助读者更好地理解和实践这个过程。 总的来说，将Excel数据导入Oracle涉及数据预处理、连接设置、选择导入方法、执行导入以及错误处理等...
这通常涉及到使用UTL_FILE包读取文件，以及BULK COLLECT和INSERT语句将数据批量插入数据库。 3. **使用ETL工具**：如Oracle的Data Integrator（ODI）或其他第三方工具如Talend、Informatica等，这些工具提供图形化...
在Oracle数据库的学习过程中，"学习oracle必备例表"是一份非常关键的资源，它包含了用于实践和理解Oracle SQL语句的示例数据。这个压缩包文件主要包括三个脚本：hr_cre.sql、hr_popul.sql和del_data.sql。下面将详细...