Oracle’s Merge v. Select->Update/Insert

mergeoracleperformance

What is the faster statement? A MERGE or Select->Update/Insert depending on if it returns a value?

I'm trying to optimize a database, but I haven't found any speed tests comparing the two.

I found the MERGE command through a StackOverflow answer

Best Answer

A MERGE would be more efficient since you're only executing the query once. Even better would be to do a set-based MERGE rather than doing a bunch of single-row MERGE statements which is what I'm assuming you'd be implementing given the alternative of doing a SELECT to see if the row exists.

Rather than doing a SELECT and then determining whether to do an INSERT or an UPDATE, you would be better off doing the UPDATE, checking to see if you updated 0 rows or not, and then doing the INSERT if your UPDATE didn't affect any rows. That way, source rows that require an UPDATE would be a bit more efficient and rows that require an INSERT would be essentially just as fast as if you had done a SELECT and INSERT. This approach is still going to be slower than a MERGE, but it's better than doing the extra SELECT.

Related Question