Recently I came across an interview question about : which is a better and faster was of updating a table in oracle DB for more than 1 Million records and why ? simple update :
update employee
set salary = salary + (salary*5%)
where dept_id = l_dept_id
or
using bulk collect and forall statement as this : `
declare
type t_ntt is table of employee.employee_id%TYPE;
l_ntt t_ntt;
l_dept_id INTEGER := 10;
c_limit INTEGER := 10000;
cursor c is select employee_id from employee where dept_id = l_dept_id;
begin
open c;
loop
fetch c into l_ntt limit c_limit;
exit when l_ntt.count = 0;
forall I in indices of l_ntt
update employee
set salary = salary + (salary*5%)
where employee_id = l_ntt(I);
end loop;
close c;
commit;
end;
`
