bulk bind

 bulk collection

when you delete a set of records that you wish to using a pl/sql program , 

oracle takes one empno number from pl/sql engine at a time to sql engine and executes it and comes back for the next one.

If you have 100 such empno-records then oracle makes 100 such trips to pl/sql and sql engines.

this trip between sql engine and pl/sql engine is called CONTEXT SWITCH.

To ensure that only one context switch is made and not 100 or more we do the following:

bulk bind the entire collection by using "FORALL syntax" to complete the job at one go.

when oracle reads FORALL it understands that the entire collection of values are to be passed to the pl/sql engine for execution and not 100 context switches.

Although the FORALL statement contains an  iteration scheme, it is not a FOR loop.

wap to read all the records from emp table and load them to a new table

emp_dup_tab, using bulk bind concept .

create table emp_dup_table as select * from emp where 1=2;

declare

type dept_tab_type is table of dept%rowtype;

my_dept dept_tab_type;

begin

select * bulk collect into my_dept from dept;

for indx in my_dept.first..my_dept.last loop

 dbms_output.put_line(  my_dept(indx).deptno||'  '||my_dept(indx).dname||'  '||my_dept(indx).loc);

 end loop;

end;

/

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

create table emp_dup_table as select * from emp where 1=2;

/

select count(*) from emp_dup_table;

create or replace procedure fast_proc is

         type emp_table_type is table of emp%ROWTYPE;

         emp_table emp_table_type;

         begin

         select  * BULK COLLECT INTO emp_table from emp;

         forall i in emp_table.First..emp_table.Last

       insert into emp_dup_table values emp_table(i) ;

       end fast_proc;

 /

exec emp_dup_table;

select count(*) from emp_dup_table;

One method of fetching data is an Oracle bulk collect. With Oracle bulk collect, the PL/SQL engine tells the SQL engine to collect many rows at once and place them in a collection.

During an Oracle bulk collect, the SQL engine retrieves all the rows and loads them into the collection and switches back to the PL/SQL engine.  When rows are retrieved using Oracle bulk collect, they are retrieved with only two context switches.  The larger the number of rows to collect  the better the performance you will see using an Oracle bulk collect.