Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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'
like image 454
DRTauli Avatar asked Sep 05 '12 03:09

DRTauli


People also ask

Is MERGE faster than insert update in Oracle?

merge is faster for merging. update is faster for updating.

Is MERGE better than update in Oracle?

Both the MERGE and UPDATE statements are designed to modify data in one table based on data from another, but MERGE can do much more. Whereas UPDATE can only modify column values you can use the MERGE statement to synchronize all data changes such as removal and addition of row.

Which is faster insert or MERGE?

Answer. Testing with a variety of source row sets against a target with about 6 mio. rows showed a slighty time advance using the merge command. Overall less internal steps are performed in the merge compared to delete/insert.

What is the difference between MERGE and insert?

MERGE is designed to apply both UPDATE and INSERTs into a target table from a source table. The statement can do both at once, or simply do INSERTs or only UPDATEs. One might even get the impression that INSERT and UPDATE are no longer needed.


1 Answers

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)
like image 132
Jeffrey Kemp Avatar answered Oct 05 '22 23:10

Jeffrey Kemp