Using Bulk Binds

Using Bulk Binds

Bulk binding refers to the ability to INSERT rows into, UPDATE rows in, and DELETE rows from a table using a collection input, or SELECT or FETCH rows from a table into a collection in a single shot without multiple context switches between SQL and PL/SQL. Bulk binding was first introduced in Oracle8i. Oracle9i enhanced bulk binding by allowing the use of bulk dynamic SQL.

How Bulk Binding Works

SQL in PL/SQL is executed by forwarding the SQL statement to the SQL engine. The output of the SQL statement is then forwarded back to the PL/SQL engine for further processing. This results in a context switch from PL/SQL to SQL and from SQL back to PL/SQL. Considering this fact, if a SQL statement is executed iteratively, the number of context switches from PL/SQL to SQL and vice versa is very large. This hinders performance.

Bulk binding minimizes this context switching. Here are the steps involved:

1. 
   

   Use the FORALL statement for bulk binding INSERT, UPDATE, and DELETE statements. Specify the SAVE EXCEPTIONS clause to filter rows rejected in the FORALL operation.

   
   


2. 
   

   Use the BULK COLLECT statement for retrieving multiple rows from a multirow query or fetching multiple rows from a cursor.

   
   


3. 
   

   The set of rows are inserted, updated, deleted, or queried in a single call with a context switch occurring from PL/SQL to SQL once and then once again from SQL to PL/SQL.

   
   


4. 
   

   Use dynamic bulk binding with EXECUTE IMMEDIATE with BULK COLLECT for SELECT queries and FETCH … BULK COLLECT INTO for bulk fetch from cursors dynamically defined in native dynamic SQL.

   
   


5. 
   

   Use EXECUTE IMMEDIATE with a FORALL statement for bulk DML and EXECUTE IMMEDIATE with a FORALL … RETURNING INTO … statement for bulk DML in native dynamic SQL.

   
   


6. 
   

   Perform multirow updates and deletes with a RETURNING clause using dynamic SQL. Prior to 9i, native dynamic SQL supported a RETURNING clause only in the case of single-row output.

   
   

Performance Benefit

As I mentioned earlier, bulk binding reduces the number of context switches between PL/SQL and SQL, resulting in faster execution of sets of DML statements. Chapter 9 contains an example in the section "Bulk DML" that verifies the performance improvement of bulk binds by performing a massive INSERT with a normal FOR LOOP and a bulk-binding FORALL statement. The example shows the time for the FORALL operation to be significantly less than that for the FOR LOOP operation.

Bulk dynamic SQL drastically improves performance by combining the performance advantage of using bulk binds to reduce context switching with the ability to execute quickly using native dynamic SQL statements.