问题描述
我有一个在WAN上运行缓慢的应用程序-我们认为原因是表中多次插入.我目前正在寻找更有效的方式来同时插入多行.
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.
我找到了这种方法:
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;
我想知道的是:上面的方法实际上比仅仅执行20次"INSERT INTO MY_TABLE(1,1);"更有效吗?还有其他方法吗?
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?
推荐答案
您可以尝试直接路径插入可以加快操作速度,但是对于100条记录而言,常规路径插入必须足够快,而且问题似乎出在从大量来源插入日志中时表锁定.
You can try direct path insert to 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.
要指示Oracle使用直接路径插入,必须指定 APPEND 或 APPEND_VALUES 提示取决于插入语句的语法.例如
To instruct Oracle to use direct path insert you must specifiy either APPEND or APPEND_VALUES hints depending on insert statement syntax. E.g.
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
)
如果insert语句起源于PL/SQL代码,则可以将批量插入与forall语句一起使用以提高性能( SQLFiddle ):
If insert statement originated from PL/SQL code then you can use bulk insert with forall statement to improve performance (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;
这篇关于Oracle 11g-插入多行的最有效方法的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!