CODEWITHSUNDEEP
sqloracle-databasesql-merge
DRTauli
DRTauli

Reputation: 771

Oracle Merge vs Select then Insert or Update

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'

Upvotes: 12

Views: 27582

Answers (2)

user6285587
user6285587

Reputation: 41

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.

Upvotes: -2

Jeffrey Kemp
Jeffrey Kemp

Reputation: 60292

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)

Upvotes: 24

Related Questions