How to bind 10 Gb of Oracle to forall bulk collect in batches

The following articles mainly introduce how to bind 10 Gb of Oracle to forall bulk collect in batches, we all know that batch binding generally reduces context switches between PL/SQL and SQL engines to improve performance. Bulk binds can be bound in batches:

(1) Input collections, use the FORALL statement to Improve the Performance of DMLINSERT, UPDATE, and DELETE operations.

(2) Output collections, use bulk collect clause, used to improve the performance of SELECT queries.

Oracle 10 Gb forall statement can start in three ways:

In low... up

In indices of collection gets the value of the lower mark of the Set element.

In values of collection gets the value of the Set element.

The forall statement can also use some set elements.

SQL % bulk_rowcount (I) indicates the number of rows that the forall statement applies to element I.

 
 

  
  
--drop table blktest;  
  
  
--CREATE TABLE blktest (num NUMBER(20), name varchar2(50));  
  
  
--CREATE OR REPLACE PROCEDURE p_bulktest　IS  
  
  
DECLARE  
  
  
TYPE type_num IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;  
  
  
TYPE type_name IS TABLE OF VARCHAR2(50) INDEX BY BINARY_INTEGER;  
  
  
tab_num type_num;  
  
  
tab_name type_name;  
  
  
t1 NUMBER;  
  
  
t2 NUMBER;  
  
  
t3 NUMBER;  
  
  
BEGIN  
  
  
FOR i IN 1 .. 500000 LOOP  
  
  
tab_num(i) := i;  
  
  
tab_name(i) := 'name: ' || to_char(i);  
  
  
END LOOP;  
  
  
SELECT dbms_utility.get_time 　  
  
  
INTO t1 　  
  
  
FROM dual;  
  
  
FOR i IN 1 .. 500000 LOOP  
  
  
INSERT INTO blktest   
  
  
VALUES  
  
  
(tab_num(i), tab_name(i));  
  
  
END LOOP;  
  
  
SELECT dbms_utility.get_time 　  
  
  
INTO t2 　  
  
  
FROM dual;  
  
  
FORALL i IN 1 .. 500000  
  
  
INSERT INTO blktest   
  
  
VALUES  
  
  
(tab_num(i), tab_name(i));  
  
  
SELECT dbms_utility.get_time 　  
  
  
INTO t3 　  
  
  
FROM dual;  
  
  
dbms_output.put_line('Execution Time(S)');  
  
  
dbms_output.put_line('-------------------');  
  
  
dbms_output.put_line('FOR loop: '   
  
  
|| to_char((t2 - t1) / 100));  
  
  
dbms_output.put_line('FORALL:　 '   
  
  
|| to_char((t3 - t2) / 100));  
  
  
END;  
  
  
/*  
  
  
Execution Time(S)  
  
  
FOR loop: 32.78  
  
  
FORALL:　 2.64  
  
  
*/  
  
  
/*  
 
 

Bulk collect statement

Used to obtain batch data. It is applicable only to the return clauses of select into, fetch into, and DML statements.

 
 

  
  
DECLARE  
  
  
TYPE type_emp IS TABLE OF   
  
  
scott.emp%ROWTYPE INDEX BY BINARY_INTEGER;  
  
  
tab_emp type_emp;  
  
  
TYPE type_ename IS TABLE OF   
  
  
scott.emp.ename%TYPE INDEX BY BINARY_INTEGER;  
  
  
tab_ename type_ename;  
  
  
CURSOR c IS  
  
  
SELECT *  
  
  
FROM scott.emp;  
  
  
BEGIN  
  
  
SELECT * BULK COLLECT  
  
  
INTO tab_emp  
  
  
FROM scott.emp;  
  
  
FOR i IN 1 .. tab_emp.COUNT LOOP  
  
  
dbms_output.put_line(tab_emp(i).ename);  
  
  
END LOOP;  
  
  
dbms_output.new_line;  
  
  
DELETE scott.emp RETURNING   
  
  
ename BULK COLLECT INTO tab_ename;  
  
  
FOR i IN 1 .. tab_emp.COUNT LOOP  
  
  
dbms_output.put_line(tab_emp(i).ename);  
  
  
END LOOP;  
  
  
ROLLBACK;  
  
  
OPEN c;  
  
  
FETCH c BULK COLLECT  
  
  
INTO tab_emp;  
  
  
dbms_output.new_line;  
  
  
FOR i IN 1 .. tab_emp.COUNT LOOP  
  
  
dbms_output.put_line(tab_emp(i).sal);  
  
  
END LOOP;  
  
  
END;  
  
  
*/  
 
 

Batch input FORALL + batch output BULK

DECLARE, batch input FORALL + batch output BULK
 

 
 

  
  
TYPE type_num IS TABLE OF NUMBER;  
  
  
tab_1 type_num;  
  
  
tab_2 type_num;  
  
  
BEGIN  
  
  
tab_1 := type_num(1, 2, 3);   
  
  
FORALL i IN 1 .. tab_1.COUNT   
  
  
--EXECUTE IMMEDIATE 'update t2 set idid2=id*2   
  
  
where id=:1 returning id2 into :2'   
  
  
--USING tab_1(i) RETURNING BULK COLLECT INTO tab_2;  
  
  
update t2 set idid2=id*2 where id=tab_1(i)   
  
  
returning id2 bulk collect into tab_2;  
  
  
FOR i IN 1 .. tab_2.COUNT LOOP  
  
  
dbms_output.put_line(tab_2(i));  
  
  
END LOOP;  
  
  
END;