代码之家 › 专栏 › 技术社区 › Sathyajith Bhat ron tornambe

批量插入到Oracle数据库:对于游标循环还是简单选择,哪个更好?

plsql oracle sql

Sathyajith Bhat ron tornambe · 技术社区 · 15 年前

类似于游标循环的

DECLARE
   CURSOR C1 IS SELECT * FROM FOO;
BEGIN
   FOR C1_REC IN C1 LOOP
   INSERT INTO BAR(A,
                B,
                C)
          VALUES(C1.A,
                 C1.B,
                 C1.C);
   END LOOP;
END

或简单的选择,如:

INSERT INTO BAR(A,
                B,
                C)
        (SELECT A,
                B,
                C
        FROM FOO);

8 回复 | 直到 13 年前

Josh Mein 15 年前

我建议使用Select选项,因为游标需要更长的时间。
对于任何需要修改查询的人来说,使用Select也更容易理解

Vadzim 10 年前

一般的经验法则是,如果您可以使用一条SQL语句而不是PL/SQL来完成,那么您应该这样做。它通常会更有效率。

例如

DECLARE
   TYPE tA IS TABLE OF FOO.A%TYPE INDEX BY PLS_INTEGER;
   TYPE tB IS TABLE OF FOO.B%TYPE INDEX BY PLS_INTEGER;
   TYPE tC IS TABLE OF FOO.C%TYPE INDEX BY PLS_INTEGER;
   rA tA;
   rB tB;
   rC tC;
BEGIN
   SELECT * BULK COLLECT INTO rA, rB, rC FROM FOO;
   -- (do some procedural logic on the data?)
   FORALL i IN rA.FIRST..rA.LAST
      INSERT INTO BAR(A,
                      B,
                      C)
      VALUES(rA(i),
             rB(i),
             rC(i));
END;

上述方法的优点是最小化了SQL和PL/SQL之间的上下文切换。Oracle11g还更好地支持记录表,这样就不必为每一列提供单独的PL/SQL表。

MichaelN 15 年前

如果回滚段/撤消段可以适应事务的大小,那么选项2更好。如果您没有所需的回滚能力,并且必须将较大的插入拆分为较小的提交,以便不会出现回滚/撤消段过小的错误,则选项1非常有用。

Scott Swank 15 年前

像您的第二个选项那样的简单插入/选择更可取。对于第一个选项中的每个插入,您需要一个从pl/sql到sql的上下文切换。使用trace/tkprof运行每个程序并检查结果。

如Michael所述,如果回滚无法处理该语句,那么让dba为您提供更多。磁盘很便宜,而通过多次插入数据得到的部分结果可能相当昂贵。(几乎没有与插入关联的撤消操作。)

sulica 13 年前

您将插入多少条记录?

如果从1到cca。10.000那么您应该使用SQL语句(就像他们说的,它很容易理解,也很容易编写)。
如果来自cca。100.000到数百万,那么您应该使用批量收集以获得更好的性能。

Arturo Hernandez 12 年前

正如你通过阅读其他答案所看到的,有很多选择。如果您只是在做<10k行您应该使用第二个选项。

简而言之,对于大约>一路跑10公里就可以说是a<10万。这是一个灰色区域。许多老家伙会在大的后退段吠叫。但老实说,硬件和软件已经取得了惊人的进步,如果只运行几次代码,您就可以在很多记录中使用选项2。否则,您可能应该每隔1k-10k左右的行提交一次。下面是我使用的一个片段。我喜欢它,因为它很短,我不必声明游标。此外,它还具有批量收集和forall的优点。

begin
    for r in (select rownum rn, t.* from foo t) loop
        insert into bar (A,B,C) values (r.A,r.B,r.C);
        if mod(rn,1000)=0 then
            commit;
        end if;
    end;
    commit;
end;

我找到了这个 link 来自oracle站点,详细说明了这些选项。

Eray Balkanli 7 年前

您可以使用:

批量收集,以及所有被调用的 Bulk binding

因为PL/SQL forall 对于简单的表格插入,操作员速度提高30倍。

BULK_COLLECT 和甲骨文 FORALL 这两个特性一起被称为 Bulk Binding . 批量绑定是一种PL/SQL技术,其中 SELECT , INSERT UPDATE 或 DELETE 语句的执行是为了从表中检索数据或将数据存储在表中,所有操作都是一次性批量执行的。这避免了当PL/SQL引擎必须先传递给SQL引擎,然后再返回到PL/SQL引擎时,以及当您一次访问一行时,出现的上下文切换。散装 插入 , 使现代化 和 删去 语句,则将SQL语句括在PL/SQL语句中 福尔 陈述散装 选择 声明,您包括 BULK COLLECT 条款语句而不是使用 INTO

它提高了性能。

Hughsmg 6 年前

我使用CREATETABLESQL,因为我发现它几乎和批量加载一样快例如,下面的create table语句用于暂存数据,将列强制转换为所需的正确数据类型:

创建表sales_dataTemp作为select 将(第1列为日期)选为销售季度, .... 从…起

这个临时表准确地反映了我的目标表的结构,它是按站点进行列表分区的。然后,我与丹佛分区进行分区交换,得到了一个新的数据集。