A m Z o n e

Reducing switches between SQL and PL/SQL (Bulk Binds)

What is bulk bind and how does it help to improve performance?

Bulk binds improve performance by minimizing the number of switches between the PL/SQL and SQL engines, it reduces network I/O. You may have a piece of code, which have multiple update, delete or insert statements on the same table. This results in multiple calls to the sql engine for carrying out the transaction and adds to network trafic. By using bulk binds, you can carry out mass scale DML's at one shot. The altered data have to be stored in a pl/sql (bind variables) table in the code. The FORALL stmt has to be used. This statement is similar to the FOR loop statement but the "loop/end loop" words are not to be used.

Example without Bulk Binds. Notice the number of times the update is performed:
E.g.:
create or replace procedure updsal is
   cursor cr_emp is
     select empno, job, sal
     from   amemp
     where  job in ('MANAGER', 'PRESIDENT');
begin
   for rec in cr_emp loop
     ... some checks on the employee
     ...
     if rec.job = 'MANAGER' then
        update amemp
        set    sal = sal + (sal * .1)
        where  empno = rec.empno;
     else
        update amemp
        set    sal = sal + (sal * .2)
        where  empno = rec.empno;
     end if;
   end loop;
end;


Example with Bulk Binding. Notice the single update call to the backend:
E.g.:
create or replace procedure updsal is
   cursor cr_emp is
     select empno, job, sal
     from   amemp
     where  job in ('MANAGER', 'PRESIDENT');
   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 + (rec.sal * .1);
     else
        empsal(cnt) := rec.sal + (rec.sal * .2);
     end if;
   end loop;

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

Press the Back button of you Browser to go to previous page
Home