Oracle合并与选择然后插入或更新 [英] Oracle Merge vs Select then Insert or Update

问题描述

什么更快?

合并语句

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

或

查询一条select语句，然后使用一条update或insert语句.

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

    SELECT * FROM table_name where row_id = 'some_id'

如果rowCount == 0

if rowCount == 0

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

其他

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

推荐答案

经验法则是，如果您可以在一个SQL中完成，通常会比在多个SQL中执行更好. SQL语句.

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)

这篇关于Oracle合并与选择然后插入或更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！