Efficient way to UPDATE bulk of records in Oracle Database

I have received many requests to write on what is the efficient way to UPDATE bulk of records in Oracle Database. With is blog I am trying to compare various ways to UPDATE one table (ROSTER) from another table (EMPLOYEE). Both tables has 999999 records and there is a PRIMARY KEY on EMPLOYEE_CODE in both tables.

With this blog I am covering following methods to UPDATE data-
1. Update each record individually and COMMIT in FOR LOOP
2. Update each record individually in FOR LOOP but COMMIT after the loop
3. BULK UPDATE using BULK COLLECT and FOR ALL
4. DIRECT UPDATE SQL
5. MERGE STATEMENT
6. UPDATE using INLINE View Method

Lets execute all and check the performance

1. Update each record individually and COMMIT in FOR LOOP

nimish@garg> begin
  2  for c in (select * from employee)
  3  loop
  4    update roster
  5    set
  6     job = c.job,
  7     position = c.position,
  8     organisation = c.organisation
  9    where
 10      employee_code = c.employee_code;
 11    commit;
 12  end loop;
 13  end;
 14  /

PL/SQL procedure successfully completed.

Elapsed: 00:03:52.36

2. Update each record individually in FOR LOOP but COMMIT after the loop

nimish@garg> begin
  2  for c in (select * from employee)
  3  loop
  4    update roster
  5    set
  6     job = c.job,
  7     position = c.position,
  8     organisation = c.organisation
  9    where
 10      employee_code = c.employee_code;
 11  end loop;  
 12  commit;
 13  end;
 14  /

PL/SQL procedure successfully completed.

Elapsed: 00:01:39.53

3. BULK UPDATE using BULK COLLECT and FOR ALL

nimish@garg> declare
  2      type employee_t is table of employee%rowtype index by pls_integer;
  3      l_employee_data   employee_t;
  4   begin
  5      select *
  6        bulk collect into l_employee_data
  7        from employee;
  8
  9      forall indx in 1 .. l_employee_data.count
 10         update roster r
 11            set r.job = l_employee_data(indx).job,
 12                r.position = l_employee_data(indx).position,
 13                r.organisation = l_employee_data(indx).organisation
 14          where r.employee_code = l_employee_data(indx).employee_code;
 15
 16      commit;
 17  end;
 18  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:43.53

4. DIRECT UPDATE SQL

nimish@garg> begin
  2  update roster r
  3     set (job, position, organisation) = (
  4          select job, position, organisation from employee e 
  5           where e.employee_code = r.employee_code
  6        )
  7  where r.employee_code in (select employee_code from employee);
  8  commit;
  9  end;
 10  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:38.22

5. MERGE STATEMENT

nimish@garg> begin
  2  merge into roster r
  3  using employee e
  4  on (r.employee_code = e.employee_code)
  5  when matched then
  6  update set
  7     r.job = e.job,
  8     r.position = e.position,
  9     r.organisation = e.organisation;
 10
 11  commit;
 12  end;
 13  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:26.23

6. UPDATE using INLINE View Method

nimish@garg> begin
  2  update
  3  (
  4    select r.employee_code, r.job, r.position, r.organisation,
  5           e.job jval, e.position pval, e.organisation oval
  6      from roster r, employee e
  7     where r.employee_code = e.employee_code
  8  )
  9  set job = jval,
 10      position = pval,
 11      organisation = oval;
 12
 13  commit;
 14  end;
 15  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:26.97

Key Take Away :-
A common misconception of many PL/SQL developer is that BULK COLLECT + FOR ALL is always better when we are doing a bulk operation. I always say BULK COLLECT + FOR ALL is better than the operation in loop, but if we can write the logic in a flat UPDATE SQL, a direct UPDATE SQL would be almost always better than the BULK COLLECT + FOR ALL. I also suggest to wrap the SQL code in a PL/SQL API.

I have not covered BULK COLLECT + FOR ALL with LIMIT clause here, as there would be some context switches and it would be little slower (very marginal depending on LIMIT Size) than BULK COLLECT + FOR ALL.

As we can see in above examples, "MERGE STATEMENT" or "UPDATE using INLINE View Method" are usually the fastest way to bulk update data, followed by a direct UPDATE STATEMENT. I usually prefer "MERGE STATEMENT" over other approaches.

Related Posts -
- Avoiding unnecessary function calls to optimize SQL statements
- Bulk Collect with Limit Clause and %NOTFOUND
- PLSQL Tuning: Bulk Collect with Dynamic SQL
- Execute Immediate Returning Bulk Collect - Dynamic DML Collection
- ORA-01555: snapshot too old
- ORA-01779: cannot modify a column which maps to a non key-preserved table