1

I have the following query and would like to some advice on how to transform it to conventional update that will not sacrifice performance of the query.

MERGE INTO TABLE_A A
USING (SELECT INSTR_ID,INSTR_DESC,INSTR_USAGE FROM TABLE_B WHERE INSTR_ID=XXX AND INSTR_USAGE IS NOT NULL) B
ON (A.INSTR_ID=B.INSTR_ID AND A.INSTR_DESC=B.INSTR_DESC)
WHEN MATCHED THEN UPDATE SET
A.INSTR_USAGE=B.INSTR_USAGE;
COMMIT;

EDIT:

Will this work? 

UPDATE TABLE_A A 
SET A.INSTR_USAGE = (SELECT INSTR_USAGE FROM TABLE_B WHERE INSTR_ID=XXX AND INSTR_USAGE IS NOT NULL )
WHERE EXISTS (SELECT 1 FROM TABLE_B WHERE A.INSTR_ID=B.INSTR_ID AND A.INSTR_DESC=B.INSTR_DESC);
user1008697
  • 1,141
  • 2
  • 10
  • 13
  • 1
    you can try this http://stackoverflow.com/questions/7030699/oracle-sql-update-with-data-from-another-table – Exhausted Mar 16 '15 at 12:01
  • Out of curiosity, why do you need to move to a conventional UPDATE statement? – Boneist Mar 16 '15 at 12:08
  • @Boneist - query plan for the merge statement requires a join with between table_a and table_b before doing the merging. I have another similar query that was accessing the same tables (executed in parallel) which caused some deadlocks issue. Hence, to avoid such issue, switching to conventinal update will prevent it from happening as UPDATE doesnt require a join in its query plan – user1008697 Mar 17 '15 at 01:11
  • 2
    Hmm, but you're going to have to use a correlated subquery in your update - what makes you think that doesn't require a join?! Also, you're going to also have to check for whether the rows in the table being updated exist in the table you're updating from, so you're going to end up having to query that table twice. I don't know about you, but that sounds less efficient to me than just doing the merge. – Boneist Mar 17 '15 at 09:11
  • +1 Boneist -- in any case, deadlocks are not caused by joins, they're caused by two sessions that each hold locks that the other needs in order to complete. If two updates are deadlocking then you can try to workaround the problem by attempting to get the updates to access the rows they need to modify in the "same order", so instead of Session A trying to update rows 1, then 2, then 3 and Session B updating rows 4, then 3, then 1, they both try to update 1 prior to 3. then you just have a regular locking situation without deadlocks. – David Aldridge Mar 18 '15 at 15:52
  • @Boneist - what u said makes sense. let me take that and ponder further what can be done. – user1008697 Mar 20 '15 at 01:57
  • @DavidAldridge - How do u go about controlling the order that Oracle will use to access the data at the row level and do the update? Merging statement doesnt allow you to have such granular control? – user1008697 Mar 20 '15 at 01:57
  • @user1008697 It ain't always easy. It generally requires an ability to control and fix the execution plan, so you are guaranteed to access the table that you are modifying in primary key order, or in the order of a particular index, or as a full table scan. That can be tricky, but by no means impossible. – David Aldridge Mar 20 '15 at 15:04

0 Answers0