Bulk Processing in Oracle

 Bulk Processing with BULK COLLECT and FORALL

BULK COLLECT: SELECT statements that retrieve multiple rows with a single fetch, improving the speed of data retrieval

FORALL: INSERTs, UPDATEs, and DELETEs that use collections to change multiple rows of data very quickly

BULK COLLECT

declare
  type t_plsql_type is table of employee%rowtype index by pls_integer;
  vt_plsql_type t_plsql_type;                      
begin

   select * bulk collect into vt_plsql_type from  employee ;

   for v_typ_idx in 1..vt_plsql_type.count loop
       dbms_output.put_line(vt_plsql_type(v_typ_idx).emp_id);
   end loop;       

end ;  

FORALL

Benefit of Cursor For loop for 10g

Which will run much slower than the other two, and why?

a.
DECLARE
CURSOR employees_cur IS SELECT * FROM employees;
BEGIN
FOR employee_rec IN employees_cur LOOP
do_stuff (employee_rec);
END LOOP;
END;

b.
DECLARE
CURSOR employees_cur IS SELECT * FROM employees;
l_employee employees%ROWTYPE;
BEGIN
OPEN employees_cur;
LOOP
FETCH employees_cur INTO l_employee;
EXIT WHEN employees_cur%NOTFOUND;
do_stuff (l_employee);
END LOOP;
CLOSE employees_cur;
END;

c.
DECLARE
CURSOR employees_cur IS SELECT * FROM employees;
TYPE employees_aat IS TABLE OF employees%ROWTYPE INDEX BY PLS_INTEGER;
l_employees employees_aat;
BEGIN
OPEN employees_cur;
LOOP
FETCH employees_cur
BULK COLLECT INTO l_employees LIMIT 100;
EXIT WHEN l_employees.COUNT () = 0;
FOR indx IN 1 .. l_employees.COUNT
LOOP
do_stuff (l_employees (indx));
END LOOP;
END LOOP;
CLOSE employees_cur;
END;

(b) Is the slowest. That's because on Oracle 10g and higher, the PL/SQL optimizer will automatically rewrite cursor FOR loops so that they are executed in the same way as the BULK COLLECT query.