Oracle Merge vs Select then Insert or Update

Question

What is faster?

the Merge statement

    MERGE INTO table_name 
     USING dual
     ON (row_id = 'some_id')
    WHEN MATCHED THEN
     UPDATE SET col_name = 'some_val'
    WHEN NOT MATCHED THEN
     INSERT (row_id, col_name)
     VALUES ('some_id', 'some_val')

or

querying a select statement then using an update or insert statement.

    SELECT * FROM table_name where row_id = 'some_id'

if rowCount == 0

    INSERT INTO table_name (row_id,col_name) VALUES ('some_id','some_val')

else

    UPDATE table_name SET col_name='some_val' WHERE row_id='some_id'

Jeffrey Kemp · Accepted Answer · 2015-07-14T00:16:43.223

23

The rule of thumb is, if you can do it in one SQL, it'll generally perform better than doing it in multiple SQL statements.

I'd go with the MERGE if it does the job.

Also - another suggestion: you can avoid repeating data in your statement, e.g.:

MERGE INTO table
 USING (SELECT 'some_id' AS newid,
               'some_val' AS newval
        FROM dual)
 ON (rowid = newid)
WHEN MATCHED THEN
 UPDATE SET colname = newval
WHEN NOT MATCHED THEN
 INSERT (rowid, colname)
 VALUES (newid, newval)

edited Jul 14 '15 at 00:16

answered Sep 05 '12 at 03:36

Jeffrey Kemp

59,135
14
106
158

I was thinking the same but how about the process on the oracle server? – DRTauli Sep 05 '12 at 03:38
yes, I gathered that - but I was talking about the choice of column name, which conflicts with oracle's ROWID pseudocolumn. – Jeffrey Kemp Sep 05 '12 at 03:44
Don't know why my answer survived so long with inaccurate information. Live and learn, I guess. – Jeffrey Kemp Jul 14 '15 at 00:17
If I use host arrays in PRO*C code for doing bulk merge with similar query, will bulk merge better that doing a query first and then do insert/update in bulk using host arrays? Please comment.Will it degrade performance as the volume increases? – Dr. Debasish Jana Feb 10 '17 at 18:57
@Dr.DebasishJana, you're more likely to get a useful answer by asking a new question. Plus, it's free! – Jeffrey Kemp Feb 11 '17 at 01:14
@JeffreyKemp yes I did, it's http://stackoverflow.com/questions/42167241/oracle-proc-bulk-merge-of-records-in-c-using-host-arrays-degrading-performance – Dr. Debasish Jana Feb 11 '17 at 05:08
The question is which one is faster? It is not which one is easy to write – MayurB Sep 17 '20 at 20:14
@MayurB, that's precisely the question I answered. That's what "perform better" means. – Jeffrey Kemp Sep 18 '20 at 03:30

score -2 · Answer 2 · edited May 03 '16 at 12:35

-2

Take care of the merge. It can consume a lot of your area TEMP using HASH JOIN. Test him using hint FIRST_ROWS or use UPDATE view join plus INSERT with NOT EXISTS.

edited May 03 '16 at 12:35

Barry Michael Doyle

9,333
30
83
143

answered May 03 '16 at 11:32

user6285587

41
2

Oracle Merge vs Select then Insert or Update

2 Answers2

Linked