提高性能的BULK COLLECT语句和FORALL语句 IN PLSQL
提高性能的BULK COLLECT语句和FORALL语句
pl/sql向sql引擎发送sql语句执行，然后SQL向pl/sql 返回结果数据。可以使用pl/sql的大批量sql特性来降低sql和pl /sql之间的通信开销。FORALL语句将INSERT或UPDATE或DELETE批次处理，BULK COLLECT子句带回批结果。如果，DML语句影响四个或更多个数据库行，使用bulk sql（批量SQL）可以显著提高性能。
1.FORALL语句的使用
但是，FORALL语句只能重复单独一条 DML语句。 例子：FORALL语句一次性把三条DELETE 语句发给SQL引擎:
1 CREATE TABLE employees_temp AS SELECT * FROM employees;
2 DECLARE
3 TYPE NumList IS VARRAY(20) OF NUMBER;
4 depts NumList := NumList(10, 30, 70); -- department numbers
5 BEGIN
6 FORALL i IN depts.FIRST..depts.LAST
7 DELETE FROM employees_temp WHERE department_id = depts(i);
8 COMMIT;
9 END;
10 /
我们可以通过下面的例子来比较FOR语句和FORALL语句的执行时间：
1 CREATE TABLE parts1 (pnum INTEGER, pname VARCHAR2(15));
2 CREATE TABLE parts2 (pnum INTEGER, pname VARCHAR2(15));
3 DECLARE
4 TYPE NumTab IS TABLE OF parts1.pnum%TYPE INDEX BY PLS_INTEGER;
5 TYPE NameTab IS TABLE OF parts1.pname%TYPE INDEX BY PLS_INTEGER;
6 pnums NumTab;
7 pnames NameTab;
8 iterations CONSTANT PLS_INTEGER := 500;
9 t1 INTEGER;
10 t2 INTEGER;
11 t3 INTEGER;
12 BEGIN
13 FOR j IN 1..iterations LOOP -- load index-by tables
14 pnums(j) := j;
15 pnames(j) := 'Part No. ' || TO_CHAR(j);
16 END LOOP;
17 t1 := DBMS_UTILITY.get_time;
18 FOR i IN 1..iterations LOOP -- use FOR loop
19 INSERT INTO parts1 VALUES (pnums(i), pnames(i));
20 END LOOP;
21 t2 := DBMS_UTILITY.get_time;
22 FORALL i IN 1..iterations -- use FORALL statement
23 INSERT INTO parts2 VALUES (pnums(i), pnames(i));
24 t3 := DBMS_UTILITY.get_time;
25 DBMS_OUTPUT.PUT_LINE('Execution Time (secs)');
26 DBMS_OUTPUT.PUT_LINE('---------------------');
27 DBMS_OUTPUT.PUT_LINE('FOR loop: ' || TO_CHAR((t2 - t1)/100));
28 DBMS_OUTPUT.PUT_LINE('FORALL: ' || TO_CHAR((t3 - t2)/100));
29 COMMIT;
30 END;
31 /
32 Execution Time (secs)
33 ---------------------
34 FOR loop: .02
35 FORALL: 0
由此，我们可以看出使用FORALL语句更快。
2.使用部分索引的FORALL语句（Part of a Collection）
此外，我们可以只使用条件集合的部分用于FORALL条件。如：
1 CREATE TABLE employees_temp AS SELECT * FROM employees;
2 DECLARE
3 TYPE NumList IS VARRAY(10) OF NUMBER;
4 depts NumList := NumList(5,10,20,30,50,55,57,60,70,75);
5 BEGIN
6 FORALL j IN 4..7 -- use only part of varray
7 DELETE FROM employees_temp WHERE department_id = depts(j);
8 COMMIT;
9 END;
10 /
3.使用非连续的稀疏索引的FORALL语句（Non-Consecutive Index Values）
同样，我们也可以使用非连续的index value（索引值）作为条件，这需要使用INDICES OF子句来处理。
1 -- Create empty tables to hold order details
2 CREATE TABLE valid_orders (cust_name VARCHAR2(32), amount NUMBER(10,2));
3 CREATE TABLE big_orders AS SELECT * FROM valid_orders WHERE 1 = 0;
4 CREATE TABLE rejected_orders AS SELECT * FROM valid_orders WHERE 1 = 0;
5 DECLARE
6 -- Make collections to hold a set of customer names and order amounts.
7 SUBTYPE cust_name IS valid_orders.cust_name%TYPE;
8 TYPE cust_typ IS TABLE OF cust_name;
9 cust_tab cust_typ;
10 SUBTYPE order_amount IS valid_orders.amount%TYPE;
11 TYPE amount_typ IS TABLE OF NUMBER;
12 amount_tab amount_typ;
13 -- Make other collections to point into the CUST_TAB collection.
14 TYPE index_pointer_t IS TABLE OF PLS_INTEGER;
15 big_order_tab index_pointer_t := index_pointer_t();
16 rejected_order_tab index_pointer_t := index_pointer_t();
17 PROCEDURE setup_data IS BEGIN
18 -- Set up sample order data, including some invalid orders and some 'big' orders.
19 cust_tab := cust_typ('Company1','Company2','Company3','Company4','Company5');
20 amount_tab := amount_typ(5000.01, 0, 150.25, 4000.00, NULL);
21 END;
22 BEGIN
23 setup_data();
24 DBMS_OUTPUT.PUT_LINE('--- Original order data ---');
25 FOR i IN 1..cust_tab.LAST LOOP
26 DBMS_OUTPUT.PUT_LINE('Customer #' || i || ', ' || cust_tab(i) || ': $' ||
27 amount_tab(i));
28 END LOOP;
29 -- Delete invalid orders (where amount is null or 0).
30 FOR i IN 1..cust_tab.LAST LOOP
31 IF amount_tab(i) is null or amount_tab(i) = 0 THEN
32 cust_tab.delete(i);
33 amount_tab.delete(i);
34 END IF;
35 END LOOP;
36 DBMS_OUTPUT.PUT_LINE('--- Data with invalid orders deleted ---');
37 FOR i IN 1..cust_tab.LAST LOOP
38 IF cust_tab.EXISTS(i) THEN
39 DBMS_OUTPUT.PUT_LINE('Customer #' || i || ', ' || cust_tab(i) || ': $' ||
40 amount_tab(i));
41 END IF;
42 END LOOP;
43 -- Because the subscripts of the collections are not consecutive, use
44 -- FORALL...INDICES OF to iterate through the actual subscripts,
45 -- rather than 1..COUNT
46 FORALL i IN INDICES OF cust_tab
47 INSERT INTO valid_orders(cust_name, amount)
48 VALUES(cust_tab(i), amount_tab(i));
49 -- Now process the order data differently
50 -- Extract 2 subsets and store each subset in a different table
51 setup_data(); -- Initialize the CUST_TAB and AMOUNT_TAB collections again.
52 FOR i IN cust_tab.FIRST .. cust_tab.LAST LOOP
53 IF amount_tab(i) IS NULL OR amount_tab(i) = 0 THEN
54 rejected_order_tab.EXTEND; -- Add a new element to this collection
55 -- Record the subscript from the original collection
56 rejected_order_tab(rejected_order_tab.LAST) := i;
57 END IF;
58 IF amount_tab(i) > 2000 THEN
59 big_order_tab.EXTEND; -- Add a new element to this collection
60 -- Record the subscript from the original collection
61 big_order_tab(big_order_tab.LAST) := i;
62 END IF;
63 END LOOP;
64 -- Now it's easy to run one DML statement on one subset of elements,
65 -- and another DML statement on a different subset.
66 FORALL i IN VALUES OF rejected_order_tab
67 INSERT INTO rejected_orders VALUES (cust_tab(i), amount_tab(i));
68 FORALL i IN VALUES OF big_order_tab
69 INSERT INTO big_orders VALUES (cust_tab(i), amount_tab(i));
70 COMMIT;
71 END;
72 /
73 -- Verify that the correct order details were stored
74 SELECT cust_name "Customer", amount "Valid order amount" FROM valid_orders;
75 SELECT cust_name "Customer", amount "Big order amount" FROM big_orders;
76 SELECT cust_name "Customer", amount "Rejected order amount" FROM rejected_orders;
