Oracle 11g - 插入多行的最有效方式
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18075220/
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
Oracle 11g - most efficient way of inserting multiple rows
提问by user1578653
I have an application which is running slowly over a WAN - we think the cause is multiple inserts into a table. I'm currently looking into more efficient ways to insert multiple rows at the same time.
我有一个在 WAN 上运行缓慢的应用程序 - 我们认为原因是多次插入到一个表中。我目前正在寻找更有效的方法来同时插入多行。
I found this method:
我找到了这个方法：
INSERT ALL
INTO MULTI_INSERT(VAL_1, VAL_2) VALUES (100,20)
INTO MULTI_INSERT(VAL_1, VAL_2) VALUES (21,2)
INTO MULTI_INSERT(VAL_1, VAL_2) VALUES (321,10)
INTO MULTI_INSERT(VAL_1, VAL_2) VALUES (22,13)
INTO MULTI_INSERT(VAL_1, VAL_2) VALUES (14,121)
INTO MULTI_INSERT(VAL_1, VAL_2) VALUES (11,112)
INTO MULTI_INSERT(VAL_1, VAL_2) VALUES (112,23)
INTO MULTI_INSERT(VAL_1, VAL_2) VALUES (132,2323)
INTO MULTI_INSERT(VAL_1, VAL_2) VALUES (121,34)
INTO MULTI_INSERT(VAL_1, VAL_2) VALUES (24333,333)
INTO MULTI_INSERT(VAL_1, VAL_2) VALUES (1232,3434)
INTO MULTI_INSERT(VAL_1, VAL_2) VALUES (4554,3434)
INTO MULTI_INSERT(VAL_1, VAL_2) VALUES (3434,211)
INTO MULTI_INSERT(VAL_1, VAL_2) VALUES (3434,1233)
INTO MULTI_INSERT(VAL_1, VAL_2) VALUES (12,22)
INTO MULTI_INSERT(VAL_1, VAL_2) VALUES (356,233)
INTO MULTI_INSERT(VAL_1, VAL_2) VALUES (9347,23)
INTO MULTI_INSERT(VAL_1, VAL_2) VALUES (8904,245)
INTO MULTI_INSERT(VAL_1, VAL_2) VALUES (342,4545)
INTO MULTI_INSERT(VAL_1, VAL_2) VALUES (453,233)
SELECT 1 FROM DUAL;
What I would like to know is: is the method above actually more efficient than just doing 20 "INSERT INTO MY_TABLE (1,1);"? Are there other methods of doing this?
我想知道的是：上述方法实际上是否比仅执行 20 个“INSERT INTO MY_TABLE (1,1);”更有效？有没有其他方法可以做到这一点？
回答by ThinkJet
You can try direct path insertto speed up operation, but for 100 records conventional path insert must be fast enough and it seems that the problem is about table locking while inserting into log from a big number of sources.
您可以尝试直接路径插入以加快操作速度，但是对于 100 条记录，常规路径插入必须足够快，而且似乎问题出在从大量来源插入日志时的表锁定问题。
To instruct Oracle to use direct path insert you must specifiy either APPENDor APPEND_VALUES hints depending on insert statement syntax. E.g.
要指示 Oracle 使用直接路径插入，您必须根据插入语句的语法指定APPEND或APPEND_VALUES提示。例如
insert /*+ APPEND */
into multi_insert(val_1, val_2)
select * from (
select 100, 20 from dual union all
select 21, 2 from dual union all
select 321, 10 from dual union all
select 22, 13 from dual union all
select 14, 121 from dual union all
select 11, 112 from dual union all
select 112, 23 from dual union all
select 132, 2323 from dual union all
select 121, 34 from dual union all
select 24333, 333 from dual union all
select 1232, 3434 from dual union all
select 4554, 3434 from dual union all
select 3434, 211 from dual union all
select 3434, 1233 from dual union all
select 12, 22 from dual union all
select 356, 233 from dual union all
select 9347, 23 from dual union all
select 8904, 245 from dual union all
select 342, 4545 from dual union all
select 453, 233 from dual
)
If insert statement originated from PL/SQL code then you can use bulk insert with forall statement to improve performance (SQLFiddle) :
如果插入语句源自 PL/SQL 代码，那么您可以使用带有 forall 语句的批量插入来提高性能 ( SQLFiddle)：
declare
type TRowList is table of multi_insert%rowtype index by binary_integer;
vRowList TRowList;
vRow multi_insert%rowtype;
begin
vRow.val_1 := 100;
vRow.val_2 := 20;
vRowList(0) := vRow;
vRow.val_1 := 21;
vRow.val_2 := 2;
vRowList(1) := vRow;
vRow.val_1 := 321;
vRow.val_2 := 10;
vRowList(2) := vRow;
-- ...
forall vIdx in vRowList.first .. vRowList.last
insert /*+ APPEND_VALUES */ -- direct path insert
into multi_insert values vRowList(vIdx);
end;
回答by APC
"a client reported that it was working perfectly when the application and Oracle were on the same LAN, but when they moved their Oracle servers abroad they say that the program is performing very slowly"
“一位客户报告说，当应用程序和 Oracle 在同一个 LAN 上时，它运行良好，但是当他们将 Oracle 服务器移到国外时，他们说程序运行非常缓慢”
Okay, so now we're getting somewhere. If you have a set-up in which your hundred statements are individual calls they will probably be sent in separate packets. That would be painful across a WAN compared to a LAN. In that case, it would be worthwhile seeing whether converting statements from RBAR to something Set-based would reduce the number of transmitted packets.
好的，现在我们到了某个地方。如果您有一个设置，其中您的一百个语句是单独的调用，它们可能会在单独的数据包中发送。与 LAN 相比，这在 WAN 上会很痛苦。在这种情况下，值得看看将语句从 RBAR 转换为基于 Set 的语句是否会减少传输的数据包数量。
However, I would still advise you to get some hard facts before you roll-out the change. Doesn't your client have a network admin you could talk to? Or at least could you get them to install Wiresharkand send you some reports?
但是，我仍然建议您在推出更改之前了解一些确凿的事实。您的客户没有可以与之交谈的网络管理员吗？或者至少你能让他们安装Wireshark并向你发送一些报告？
回答by Jorge Santos Neill
Oracle doesnt support multi rows insert, please use the next option:
Oracle 不支持多行插入，请使用下一个选项：
insert into method (name) values ('GET'); insert into method (name) values ('POST');
插入方法（名称）值（'GET'）；插入方法（名称）值（'POST'）；
回答by dani herrera
Some RDBMS like mysql and now SQL Server supports multiple rows insert data syntax:
一些 RDBMS 像 mysql 和现在 SQL Server 支持多行插入数据语法：
Insert into myTable ( c1, c2 ) values
( 1,1 ),
( 1,2 ),
... ;
( More details in Inserting multiple rows of data of Sql Serveror inserting multirow on mysql)
（更多细节在Sql Server 的插入多行数据或在 mysql 上插入多行）
But don't oracle. Sorry about bad news. The more close way is documented on Tech on the Net.
但不要神谕。对坏消息表示抱歉。更接近的方式记录在Tech on the Net 上。
