G-3210: Always use BULK OPERATIONS (BULK COLLECT, FORALL) whenever you have to execute a DML statement for more than 4 times.

Major

Efficiency

Reason

Context switches between PL/SQL and SQL are extremely costly. BULK Operations reduce the number of switches by passing an array to the SQL engine, which is used to execute the given statements repeatedly.

(Depending on the PLSQL_OPTIMIZE_LEVEL parameter a conversion to BULK COLLECT will be done by the PL/SQL compiler automatically.)

Example (bad)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
declare
   t_employee_ids employee_api.t_employee_ids_type;
   k_increase constant employee.salary%type := 0.1;
   k_department_id constant departments.department_id%type := 10;
begin
   t_employee_ids := employee_api.employee_ids_by_department(
                        id_in => k_department_id
                     );
   <<process_employees>>
   for i in 1..t_employee_ids.count()
   loop
      update employee
         set salary = salary + (salary * k_increase)
       where employee_id = t_employee_ids(i);
   end loop process_employees;
end;
/

Example (good)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
declare
   t_employee_ids  employee_api.t_employee_ids_type;
   k_increase      constant employee.salary%type := 0.1;
   k_department_id constant departments.department_id%type := 10;
begin
   t_employee_ids := employee_api.employee_ids_by_department(
                        id_in => k_department_id
                     );
   <<process_employees>>
   forall i in 1..t_employee_ids.count()
      update employee
         set salary = salary + (salary * k_increase)
       where employee_id = t_employee_ids(i);
end;
/