As the following figure shows, during the execution of a PL/SQL block, the PL/SQL engine executes procedural statements but sends SQL statements to the SQL engine, which executes the SQL statements and, in some cases, returns data to the PL/SQL engine. 

So when a PL/SQL block including a loop, which causes frequently transfer between PL/SQL engine and SQL engine, the time cost is quite high and the efficiency is really low.

For example:

DECLARE
   TYPE NumList IS VARRAY(20) OF NUMBER;
   depts NumList := NumList(10, 30, 70);  -- department numbers
BEGIN
   ...
   FOR i IN depts.FIRST..depts.LAST LOOP
      DELETE FROM emp WHERE deptno = depts(i);
   END LOOP;
END;

The above DELETE statement is sent to the SQL engine with each iteration of the FOR loop. Too many context switches between the PL/SQL and SQL engines and it can harm performance. So, what should we do to improve the performance? Use Bulk Binds!

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 or UPDATE statement.
• out-bind When a database value is assigned to a PL/SQL variable or a host variable by the RETURNING clause of an INSERT, UPDATE, or DELETE statement.
• define When a database value is assigned to a PL/SQL variable or a host variable by a SELECT or FETCH 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

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;

 

Using the FORALL Statement

        The keyword FORALL instructs the PL/SQL engine to bulk-bind input collections before sending them to the SQL engine. Although the FORALL statement contains an iteration scheme, it is not a FOR loop. Its syntax follows:

FORALL INDEX IN lower_bound..upper_bound
   sql_statement;
 

      The index can be referenced only within the FORALL statement and only as a collection subscript. The SQL statement must be an INSERT, UPDATE, or DELETE statement that references collection elements. And, the bounds must specify a valid range of consecutive index numbers. The SQL engine executes the SQL statement once for each index number in the range.

Retrieving Query Results into Collections with the BULK COLLECT Clause

        The keywords BULK COLLECT tell the SQL engine to bulk-bind output collections before returning them to the PL/SQL engine. You can use these keywords in the SELECT INTO, FETCH INTO, and RETURNING INTO clauses. Here is the syntax:

... BULK COLLECT INTO collection_name[, collection_name] ...

        The SQL engine bulk-binds all collections referenced in the INTO list. The corresponding columns can store scalar or composite values including objects. In the following example, the SQL engine loads the entire empno and ename database columns into nested tables before returning the tables to the PL/SQL engine:

DECLARE
   TYPE NumTab IS TABLE OF emp.empno%TYPE;
   TYPE NameTab IS TABLE OF emp.ename%TYPE;
   enums NumTab;  -- no need to initialize
   names NameTab;
BEGIN
   SELECT empno, ename BULK COLLECT INTO enums, names FROM emp;
   ...
END;

        The SQL engine bulk-binds entire database columns. So, if a table has 50,000 rows, the engine loads 50,000 column values into the target collection. However, you can use the pseudocolumn ROWNUM to limit the number of rows processed. In the following example, you limit the number of rows to 100:

DECLARE
   TYPE SalList IS TABLE OF emp.sal%TYPE;
   sals SalList;
BEGIN
   SELECT sal BULK COLLECT INTO sals FROM emp
      WHERE ROWNUM <= 100;
   ...
END;

Examples of Bulk Fetching from a Cursor

DECLARE
   TYPE NameList IS TABLE OF emp.ename%TYPE;
   TYPE SalList IS TABLE OF emp.sal%TYPE;
   CURSOR c1 IS SELECT ename, sal FROM emp WHERE sal > 1000;
   names NameList;
   sals  SalList;
BEGIN
   OPEN c1;
   FETCH c1 BULK COLLECT INTO names, sals;
END;

Limiting the Rows for a Bulk FETCH Operation with the LIMIT Clause

The optional LIMIT clause, allowed only in bulk (not scalar) FETCH statements, lets you limit the number of rows fetched from the database. The syntax is

FETCH ... BULK COLLECT INTO ... [LIMIT rows];

where rows can be a literal, variable, or expression but must evaluate to a number. Otherwise, PL/SQL raises the predefined exception VALUE_ERROR. If the number is not positive, PL/SQL raises INVALID_NUMBER. If necessary, PL/SQL rounds the number to the nearest integer.

DECLARE
   TYPE NumTab IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
   CURSOR c1 IS SELECT empno FROM emp;
   empnos NumTab;
   rows   NATURAL := 10;
BEGIN
   OPEN c1;
   LOOP
      /* The following statement fetches 10 rows (or less). */
      FETCH c1 BULK COLLECT INTO empnos LIMIT rows;
      EXIT WHEN c1%NOTFOUND;
      ...
   END LOOP;
   CLOSE c1;
END;

 Retrieving DML Results into a Collection with the RETURNING INTO Clause

        You can use the BULK COLLECT clause in the RETURNING INTO clause of an INSERT, UPDATE, or DELETE statement, as the following example shows:

DECLARE
   TYPE NumList IS TABLE OF emp.empno%TYPE;
   enums NumList;
BEGIN
   DELETE FROM emp WHERE deptno = 20
      RETURNING empno BULK COLLECT INTO enums;
      -- if there were five employees in department 20,
      -- then enums contains five employee numbers
END;

Restrictions on BULK COLLECT

        The following restrictions apply to the BULK COLLECT clause:

·                You cannot bulk collect into an associative array that has a string type for the key.

·                You can use the BULK COLLECT clause only in server-side programs (not in client-side programs). Otherwise, you get the error this feature is not supported in client-side programs.

·                All targets in a BULK COLLECT INTO clause must be collections, as the following example shows:

            DECLARE
                   TYPE NameList IS TABLE OF emp.ename%TYPE;
                   names  NameList;
                salary emp.sal%TYPE;
                BEGIN
                   SELECT ename, sal BULK COLLECT INTO names, salary  -- illegal target
                      FROM emp WHERE ROWNUM < 50;
                   ...
                END;                

More information, please refer to :

http://download.oracle.com/docs/cd/B10501_01/appdev.920/a96624/05_colls.htm#23723

• Relative Posts
• Global Relative Posts