Skip to content

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

Critical

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;
   co_increase      constant employees.salary%type          := 0.1;
   co_department_id constant departments.department_id%type := 10;
begin
   t_employee_ids := employee_api.employee_ids_by_department(
                        id_in => co_department_id
                     );
   <<process_employees>>
   for i in 1..t_employee_ids.count()
   loop
      update employees
         set salary = salary + (salary * co_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;
   co_increase      constant employees.salary%type          := 0.1;
   co_department_id constant departments.department_id%type := 10;
begin
   t_employee_ids := employee_api.employee_ids_by_department(
                        id_in => co_department_id
                     );
   <<process_employees>>
   forall i in 1..t_employee_ids.count()
      update employees
         set salary = salary + (salary * co_increase)
       where employee_id = t_employee_ids(i);
end;
/