How Do Bulk Binds Improve Performance?
The assigning of values to PL/SQL variables in SQL statements is called binding. PL/SQL binding operations fall into three categories:
- in-bind When a PL/SQL variable or host variable is stored in the database by an
INSERT
orUPDATE
statement. - out-bind When a database value is assigned to a PL/SQL variable or a host variable by the
RETURNING
clause of anINSERT
,UPDATE
, orDELETE
statement. - define When a database value is assigned to a PL/SQL variable or a host variable by a
SELECT
orFETCH
statement.
A DML statement can transfer all the elements of a collection in a single operation, a process known as bulk binding. If the collection has 20 elements, bulk binding lets you perform the equivalent of 20 SELECT
, INSERT
, UPDATE
, or DELETE
statements using a single operation. This technique improves performance by minimizing the number of context switches between the PL/SQL and SQL engines. With bulk binds, entire collections, not just individual elements, are passed back and forth.
To do bulk binds with INSERT
, UPDATE
, and DELETE
statements, you enclose the SQL statement within a PL/SQL FORALL
statement.
To do bulk binds with SELECT
statements, you include the BULK
COLLECT
clause in the SELECT
statement instead of using INTO
.
For full details of the syntax and restrictions for these statements, see "FORALL Statement" and "SELECT INTO Statement".
Example: Performing a Bulk Bind with DELETE
The following DELETE
statement is sent to the SQL engine just once, even though it performs three DELETE
operations:
DECLARE TYPE NumList IS VARRAY(20) OF NUMBER; depts NumList := NumList(10, 30, 70); -- department numbers BEGIN FORALL i IN depts.FIRST..depts.LAST DELETE FROM emp WHERE deptno = depts(i); END;
Example: Performing a Bulk Bind with INSERT
In the example below, 5000 part numbers and names are loaded into index-by tables. All table elements are inserted into a database table twice: first using a FOR
loop, then using a FORALL
statement. The FORALL
version is much faster.
SQL> SET SERVEROUTPUT ON SQL> CREATE TABLE parts (pnum NUMBER(4), pname CHAR(15)); Table created. SQL> GET test.sql 1 DECLARE 2 TYPE NumTab IS TABLE OF NUMBER(4) INDEX BY BINARY_INTEGER; 3 TYPE NameTab IS TABLE OF CHAR(15) INDEX BY BINARY_INTEGER; 4 pnums NumTab; 5 pnames NameTab; 6 t1 NUMBER(5); 7 t2 NUMBER(5); 8 t3 NUMBER(5); 9 10 11 BEGIN 12 FOR j IN 1..5000 LOOP -- load index-by tables 13 pnums(j) := j; 14 pnames(j) := 'Part No. ' || TO_CHAR(j); 15 END LOOP; 16 t1 := dbms_utility.get_time; 17 FOR i IN 1..5000 LOOP -- use FOR loop 18 INSERT INTO parts VALUES (pnums(i), pnames(i)); 19 END LOOP; 20 t2 := dbms_utility.get_time; 21 FORALL i IN 1..5000 -- use FORALL statement 22 INSERT INTO parts VALUES (pnums(i), pnames(i)); 23 get_time(t3); 24 dbms_output.put_line('Execution Time (secs)'); 25 dbms_output.put_line('---------------------'); 26 dbms_output.put_line('FOR loop: ' || TO_CHAR(t2 - t1)); 27 dbms_output.put_line('FORALL: ' || TO_CHAR(t3 - t2)); 28* END; SQL> / Execution Time (secs) --------------------- FOR loop: 32 FORALL: 3 PL/SQL procedure successfully completed.