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;
Best viewed in medium text size. Please refresh this page (F5) to view the latest information. This page was created on 16-dec-2001 and last updated on 16-dec-2001.
please forward all queries to amar_padhi@fastmail.fm