Bulk Binds and Collects in PL/SQL - Part - 1

Tuesday Jun 22nd 2004 by Amar Kumar Padhi
Share:

This article begins a discussion of how to work with collections. Previous versions of Oracle had limitations for collection usage in regards to SQL statement processing. Bulk Bind and Collect features were introduced to reduce the SQL processing overhead by efficient use of collections in PL/SQL code.

This article begins a discussion of how to work with collections. Previous versions of Oracle had limitations for collection usage in regards to SQL statement processing. Bulk Bind and Collect features were introduced to reduce the SQL processing overhead by efficient use of collections in PL/SQL code.

The PL/SQL engine executes procedural statements and sends all SQL statements present in the code to the SQL engine. The SQL engine will parse and execute the query or DML statement and return the expected output back to the PL/SQL engine. This switch between the two engines is called context switching.

We mostly concentrate on the SQL statement to tune performance issues. It is worth noting that excessive context switching can affect performance. This would be substantially significant when we are carrying out SQL statements in a loop. The features discussed below were introduced to reduce this overhead of SQL processing. Introduced in Oracle 8i, these features are being improved on with every new release.

Two PL/SQL features, Bulk Bind and Bulk collect help in improving performance and utilizing resources effectively from within PL/SQL code. These features reduce context switching, (i.e., multiple SQL statements being executed from the code resulting in calls to SQL engine), and carry out the operation on the concerned object at one go. Since the SQL statements are fired in a bundle, frequent access to SQL engine is reduced.

In cases where the PL/SQL code is being executed on a different terminal than the server itself, this would also result in optimal network usage rather than too frequent network calls to access the SQL engine.

Bulk Collects (Reading data in bulk)

The bulk collect option instructs the SQL engine to bulk bind the output collections before returning them to the PL/SQL engine. This allows us to load data dynamically into collections at one shot for further processing. Bulk collect can be used with SELECT INTO, FETCH INTO and RETURNING INTO statements.

Syntax:

  ... bulk collect into collection...

For example, let us assume that we need to load all pending transactions into a temporary table and process them one by one. As part of validation, there is a need to refer to the data in the same table, from time to time, for each transaction being processed. One possible method to write the code would be to load all of the data in the temporary table to a collection type. This way, additional queries on the table could be avoided (context switch) and the processing could be carried out in PL/SQL itself. This idea is further improved on by the use of the bulk collect option, as all data is loaded into PL/SQL at the same time.

declare
  type pndidr is table of mtl_pending_trx.trx_id%type index by binary_integer;
  type pndqty is table of mtl_pending_trx.trx_qty%type index by binary_integer;
  type pndval is table of mtl_pending_trx.trx_cost%type index by binary_integer;

  l_pndidr pndidr;
  l_pndqty pndqty;
  l_pndval pndval;
  ...
begin
  select trx_id, trx_qty, trx_cost * trx_qty  bulk collect
  into   l_pndidr, l_pndqty, l_pndval
  from   mtl_pending_trx;
  
  ...
  for i in l_pndidr.first .. l_pndidr.last loop
    dbms_output.put_line(l_pndidr(i));
    dbms_output.put_line(l_pndqty(i));
    dbms_output.put_line(l_pndval(i));
  end loop;
  ...
end;


Bulk Binds (Writing data in bulk)



Bulk binds improve performance of DML statements by minimizing the number of switches between the PL/SQL and SQL engines.



You may have a piece of code, which has multiple update, delete or insert statements on the same table. This results in multiple calls to the SQL engine for carrying out the transaction. By using bulk binds, you can carry out mass scale data manipulation at one shot. The altered data has to be stored in a PL/SQL collection in the code.



The FORALL statement is used for doing the bulk-processing job at one go. This statement is similar to the FOR-LOOP statement except that LOOP/END LOOP key words are not used. The FORALL statement needs a range to work on, along with whatever DML activity is to be carried.



Syntax:

  forall <counter> in <range begin> .. <range end>
    update/insert/delete

Below is a simple example without Bulk Binds. Notice the number of times the update is performed. Every SQL statement present in the PL/SQL code results in a call to the SQL engine for processing.

create or replace procedure updsal is
   cursor cr_emp is
     select empno, job, sal
     from   amemp
     where  job in ('MANAGER', 'PRESIDENT', 'DBA');
begin
   for rec in cr_emp loop
     ... some checks on the employee
     ...
     if rec.job = 'MANAGER' then
        update amemp
        set    sal = sal * 1.1
        where  empno = rec.empno;
     else
        update amemp
        set    sal = sal * 1.2
        where  empno = rec.empno;
     end if;
   end loop;
end;

The above code is changed as shown below, using the bulk binding option. Notice the single update call to the backend.

create or replace procedure updsal is
   cursor cr_emp is
     select empno, job, sal
     from   amemp
     where  job in ('MANAGER', 'PRESIDENT', 'DBA');

   type amemp_tab1 is table of amemp.empno%type
        index by binary_integer;

   type amemp_tab2 is table of amemp.sal%type
        index by binary_integer;

   empnum amemp_tab1;
   empsal amemp_tab2;
   cnt   number := 0;
begin
   for rec in cr_emp loop
     ... some checks on the employee
     ...

     cnt := cnt + 1;
     empnum(cnt) := rec.empno;

     if rec.job = 'MANAGER' then
        empsal(cnt) := rec.sal * 1.1;
     else
        empsal(cnt) := rec.sal * 1.2;
     end if;
   end loop;

   forall i in 1 .. cnt
     update amemp
     set    sal = empsal(i)
     where  empno = empnum(i);
end;
/

Below is an example that uses both bulk binding and collection together.

declare
  type emp_t is table of amemp.empno%type;
  lst  emp_t;
begin
  select empno bulk collect 
  into   lst
  from   amemp
  where  sal < 2000;

  ..some checks here...

  forall i in lst.first .. lst.last
    update amemp
    set    sal = sal * 1.1
    where  empno = lst(i);
end;
/

Bulk collects updated value return

You can even use bulk collects to return a value to the calling procedure using the RETURNING clause, without any additional fetch. Below is an example that updates the salary as and stores the updated information in a collection for further processing.

declare
  type no_t  is table of amemp.empno%type;
  type sal_t is table of amemp.sal%type;
  lno  no_t;
  lsal sal_t;

begin
  select empno bulk collect
  into   lno
  from   amemp
  where  job = 'DBA';
  ..
  ..
  forall i in lno.first .. lno.last
    update amemp
    set    sal =  sal * 1.1
    where  empno = lno(i)
    returning sal bulk collect into lsal;
 ..
  for i in lno.first .. lno.last loop
    dbms_output.put_line(lno(i) || '/' || lsal(i));
  end loop;
end;
/

In the next installment, we will cover the above features with more collection examples and performance issues that may be encountered in heavy processing.

» See All Articles by Columnist Amar Kumar Padhi

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved