Oracle Merge vs Select then Insert (Insert-select) or Update

Diferencias entre Merge vs Insert-Select:  Importante el Merge no falla si no hay datos, mientras que Insert-Select si da un fallo.

Viene por la pregunta: 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'

---

RESPUESTA A LA PREGUNTA:

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);

--

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.

Fuentes

Artículo:   "Oracle Merge vs Select then Insert or Update " Publicado en https://stackoverflow.com/ el 05/09/2012.  consultado el  3 may 2025

URL: https://stackoverflow.com/questions/12274156/oracle-merge-vs-select-then-insert-or-update