Bulk  Mechanism

IT IS USED TO REDUCE THE TIME TAKEN TO RETREVE THE DATA

àBulk is one of the method to improve the performance of the applications

2 types:

                1:Bulk Collect

                2:Bulk binding

 1:Bulk Collect    In this clause we are used to fetch the data from database  into collection.

                            àselect ……into…clause

                            àcursor ….fetch…..statement

                            àdml ….returning

bulk collect practise

=====================

declare

type t1 is table of jacmel.jmplan_temp%rowtype index by binary_integer;

v1   t1;

begin

select * bulk collect into v1 from jacmel.jmplan_temp;

for i in v1.first..v1.last

loop

dbms_output.put_line(v1(i).SEGMENT1||','||v1(i).ATTR1||','||v1(i).CUSTOMER_NUMBER);

end loop;

end;

Example for bulk collect by 2 variables

---------------------------------------

DECLARE

type t1

IS

  TABLE OF VARCHAR2(50) INDEX BY binary_integer;

  v1 t1;

  v2 t1;

  CURSOR c1

  IS

    SELECT SEGMENT1,attr1 FROM jacmel.jmplan_temp;

BEGIN

  OPEN c1;

  FETCH c1 bulk collect INTO v1,v2;

  CLOSE c1;

  FOR i IN v1.first..v1.last

  LOOP

    dbms_output.put_line(v1(i)||','||v1(i));

  END LOOP;

END;

example with out bulk collect method

declare

vrow VARCHAR2(30);

cursor c1 is select distinct name from wsh_deliverable_trips_v;

z1 number(10);

z2 number(10);

begin

z1:=dbms_utility.get_time;

open c1;

loop

fetch c1 into vrow;

exit when c1%notfound;

end loop;

close c1;

z2:=dbms_utility.get_time;

dbms_output.put_line(z1);

dbms_output.put_line(z2);

dbms_output.put_line(z2-z1);

end;

/

example with bulk method

declare

type t1 is table of varchar2(30) index by binary_integer;

v1 t1;

cursor c1 is select distinct name from wsh_deliverable_trips_v;

z1 number(10);

z2 number(10);

begin

z1:=dbms_utility.get_time;

open c1;

loop

fetch c1 bulk collect into v1;

exit when c1%notfound;

end loop;

close c1;

z2:=dbms_utility.get_time;

dbms_output.put_line(z1);

dbms_output.put_line(z2);

dbms_output.put_line(z2-z1);

end;

/