Update vs Merge in Oracle for a list of record not for bulk record set ?Performance?

Question

Currently I have many update Statements in my project ..What i am doing is passing a list then looping it in DataAccess layer and updating the database

Let say i have a list of record contains Comment,userID and ID ..I am updating based on ID in a loop ...is there any better way to do it ..

Can i use Merge statement ? will it increase performance ?whats the exact difference?

UPDATE RecordTable
SET
COMMENT=:COMMENT,
MODIFIEDDate = SYSTIMESTAMP, 
UserID = :UserID 
WHERE ID = :ID

Solution 1

Merge will be better then update for the simple reason that executing an update one row at a time causes a lot of unnecessary context switched and index / table access . Oracle operates better in bulks.

To be able to use a merge statement here you'll have to create an intermediate table with ID,COMMENT and USERID.

only after you can execute the merge

merge into RecordTable a using TEMP_recordtable b 
on (a.id = b.id) 
when matched then update set 
 a.COMMENT=b.COMMENT ,
 a.MODIFIEDDate = SYSTIMESTAMP, 
 a.UserID = b.UserID

EDIT: updating without temp table

Demo table

SQL> create table m1 (id number , name varchar2(30) , updated date);
Table created.
SQL> insert into m1 values (1 , 'Haki', sysdate);
1 row created.
SQL> insert into m1 values (3 , 'Simon', sysdate);
1 row created.
SQL> commit;
SQL> select * from m1;

        ID NAME                           UPDATED
---------- ------------------------------ -------------------
         1 Haki                           03/10/2013 09:39:37
         3 Simon                          03/10/2013 09:38:17

If you want to use collections in sql you need to declare them in the db

SQL> create type rec as object (id number , name varchar2(10))
  2  /
Type created.

SQL> create type rec_arr as table of rec;
  2  /
Type created.

now we create the list and merge it to our table

SQL> ed
Wrote file afiedt.buf

  1  declare
  2  myarr rec_arr := rec_arr( rec (1 , 'Haki') , rec (2 , 'Raul'));
  3  begin
  4     merge into m1 using table(myarr) b on (m1.id = b.id)
  5     when matched then update set
  6             m1.name = b.name ,
  7             m1.updated = sysdate
  8     when not matched then insert (id , name , updated)
  9     values (b.id , b.name , sysdate);
 10* end;
SQL> /

PL/SQL procedure successfully completed.

SQL> select * from m1;

        ID NAME                           UPDATED
---------- ------------------------------ -------------------
         1 Haki                           03/10/2013 09:40:16
         3 Simon                          03/10/2013 09:38:17
         2 Raul                           03/10/2013 09:40:16

3 rows selected.

As you can see, existing records has been updated , New records are inserted.

Solution 2

You can perform updates in bulk just as easily as you can perform a merge.

You load the required update values into a global temporary table and ensure that there is a primary or unique key constraint in place on the join key with the table to be updated. You run the update against a join of the two tables, similar to:

update (
  select t.pk,
         t.old_value,
         s.new_value
  from   target_table t
  join   source_table s on (s.pk = t.some_column))
set old_value = new_value.

As long as the inline view is "key-preserved" the update is going to be just as fast as a merge.

http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_10008.htm

Similarly you can perform a delete against a key-preserved view or inline view.

Solution 3

As others have pointed out, context switches are horrible for performance. But keep in mind that the law of diminishing returns kicks in very quickly when reducing context switches. Bulking 10 rows at a time reduces them by 90%, bulking 100 rows reduces them by 99%, etc. To get most of the performance gain you only need to combine a relatively small number of statements.

Many environments have a feature that does this automatically. Such as PL/SQL forall or JDBC batching. If those are not available, you can accomplish this by manually grouping sets of data together in a larger statement. For example:

merge into RecordTable
using
(
    select :id1 id, :comment1 comment, :userID1 userID from dual union all
    select :id2 id, :comment2 comment, :userID2 userID from dual union all
    ...
    select :id10 id, :comment10 comment, :userID10 userID from dual
) new_data
on RecordTable.id = new_data.id
when matched then update set
    RecordTable.comment = new_data.comment,
    RecordTable.modfifiedDate = systimestamp,
    RecordTable.UserId = new_data.userID;

For the left-over rows, use null for the ID and those records will not match anything.

If your question is only about merge vs. update, the most significant performance difference is that merge can support hash joins and update cannot. But that does not matter here.