在 Oracle 中更新与合并以获得不用于批量记录集的记录列表？性能？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19151288/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Update vs Merge in Oracle for a list of record not for bulk record set ?Performance?
提问by Rahul Chowdhury
Currently I have many update Statements in my project ..What i am doing is passing a list then looping it in DataAccess layer and updating the database
目前我的项目中有很多更新语句..我正在做的是传递一个列表，然后在 DataAccess 层中循环它并更新数据库
Let say i have a list of record contains Comment,userID and ID ..I am updating based on ID in a loop ...is there any better way to do it ..
假设我有一个包含评论、用户 ID 和 ID 的记录列表......我正在循环中根据 ID 进行更新......有没有更好的方法来做到这一点......
Can i use Merge statement ? will it increase performance ?whats the exact difference?
我可以使用合并语句吗？它会提高性能吗？确切的区别是什么？
UPDATE RecordTable
SET
COMMENT=:COMMENT,
MODIFIEDDate = SYSTIMESTAMP,
UserID = :UserID
WHERE ID = :ID
回答by haki
Merge will be better then update for the simple reason that executing an update one row at a time causes a lot of unnecessary context switched and index / table access . Oracle operates better in bulks.
合并会比更新更好，原因很简单，一次执行更新一行会导致大量不必要的上下文切换和索引/表访问。甲骨文在批量操作中表现更好。
To be able to use a merge statement here you'll have to create an intermediate table with ID,COMMENTand USERID.
为了能够在这里使用合并语句，您必须创建一个带有ID,COMMENT和的中间表USERID。
only after you can execute the merge
只有在您可以执行合并之后
merge into RecordTable a using TEMP_recordtable b
on (a.id = b.id)
when matched then update set
a.COMMENT=b.COMMENT ,
a.MODIFIEDDate = SYSTIMESTAMP,
a.UserID = b.UserID
EDIT:updating without temp table
编辑：更新没有临时表
Demo table
演示表
SQL> create table m1 (id number , name varchar2(30) , updated date);
Table created.
SQL> insert into m1 values (1 , 'Haki', sysdate);
1 row created.
SQL> insert into m1 values (3 , 'Simon', sysdate);
1 row created.
SQL> commit;
SQL> select * from m1;
ID NAME UPDATED
---------- ------------------------------ -------------------
1 Haki 03/10/2013 09:39:37
3 Simon 03/10/2013 09:38:17
If you want to use collections in sql you need to declare them in the db
如果要在 sql 中使用集合，则需要在 db 中声明它们
SQL> create type rec as object (id number , name varchar2(10))
2 /
Type created.
SQL> create type rec_arr as table of rec;
2 /
Type created.
now we create the list and merge it to our table
现在我们创建列表并将其合并到我们的表中
SQL> ed
Wrote file afiedt.buf
1 declare
2 myarr rec_arr := rec_arr( rec (1 , 'Haki') , rec (2 , 'Raul'));
3 begin
4 merge into m1 using table(myarr) b on (m1.id = b.id)
5 when matched then update set
6 m1.name = b.name ,
7 m1.updated = sysdate
8 when not matched then insert (id , name , updated)
9 values (b.id , b.name , sysdate);
10* end;
SQL> /
PL/SQL procedure successfully completed.
SQL> select * from m1;
ID NAME UPDATED
---------- ------------------------------ -------------------
1 Haki 03/10/2013 09:40:16
3 Simon 03/10/2013 09:38:17
2 Raul 03/10/2013 09:40:16
3 rows selected.
As you can see, existing records has been updated , New records are inserted.
如您所见，现有记录已更新，新记录已插入。
回答by David Aldridge
You can perform updates in bulk just as easily as you can perform a merge.
您可以像执行合并一样轻松地执行批量更新。
You load the required update values into a global temporary table and ensure that there is a primary or unique key constraint in place on the join key with the table to be updated. You run the update against a join of the two tables, similar to:
您将所需的更新值加载到全局临时表中，并确保在要更新的表的连接键上存在主键或唯一键约束。您针对两个表的连接运行更新，类似于：
update (
select t.pk,
t.old_value,
s.new_value
from target_table t
join source_table s on (s.pk = t.some_column))
set old_value = new_value.
As long as the inline view is "key-preserved" the update is going to be just as fast as a merge.
只要内联视图是“键保留的”，更新就会和合并一样快。
http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_10008.htm
http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_10008.htm
Similarly you can perform a delete against a key-preserved view or inline view.
同样，您可以对保留键的视图或内联视图执行删除。
回答by Jon Heller
As others have pointed out, context switches are horrible for performance. But keep in mind that the law of diminishing returns kicks in very quickly when reducing context switches. Bulking 10 rows at a time reduces them by 90%, bulking 100 rows reduces them by 99%, etc. To get most of the performance gain you only need to combine a relatively small number of statements.
正如其他人指出的那样，上下文切换对性能来说是可怕的。但请记住，当减少上下文切换时，收益递减定律会很快生效。一次增加 10 行会减少 90%，增加 100 行会减少 99%，等等。要获得大部分性能增益，您只需要组合相对较少的语句。
Many environments have a feature that does this automatically. Such as PL/SQL forallor JDBC batching. If those are not available, you
can accomplish this by manually grouping sets of data together in a larger statement. For example:
许多环境具有自动执行此操作的功能。例如 PL/SQLforall或 JDBC 批处理。如果这些不可用，您可以通过在更大的语句中手动将数据集分组在一起来完成此操作。例如：
merge into RecordTable
using
(
select :id1 id, :comment1 comment, :userID1 userID from dual union all
select :id2 id, :comment2 comment, :userID2 userID from dual union all
...
select :id10 id, :comment10 comment, :userID10 userID from dual
) new_data
on RecordTable.id = new_data.id
when matched then update set
RecordTable.comment = new_data.comment,
RecordTable.modfifiedDate = systimestamp,
RecordTable.UserId = new_data.userID;
For the left-over rows, use nullfor the ID and those records will not match anything.
对于剩余的行，null用于 ID 并且这些记录将不匹配任何内容。
If your question is only about mergevs. update, the most significant performance difference is that mergecan support hash joins and updatecannot. But that does not matter here.
如果您的问题只是关于mergevs. update，那么最显着的性能差异是merge可以支持散列连接，而update不能。但这在这里无关紧要。
