sql - Oracle 11g - 插入多行的最有效方法

Question

我有一个应用程序在 WAN 上运行缓慢 - 我们认为原因是对表进行多次插入。我目前正在研究同时插入多行的更有效方法。

我找到了这个方法:

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);”更有效吗？还有其他方法可以做到这一点吗？

Answer 1

您可以尝试direct path insert为了加快操作速度，但是对于 100 条记录，传统路径插入必须足够快，而且问题似乎在于从大量源插入日志时的表锁定。

要指示 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
)

如果插入语句源自 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;