oracle 有没有办法在指定提交计数时执行 INSERT INTO SELECT 语句？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10176722/
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
Is there a way to do an INSERT INTO SELECT statement while specifying a commit count?
提问by ScrappyDev
DB: Oracle 11g
数据库：Oracle 11g
Is there a way to do something like the following:
有没有办法做类似以下的事情：
INSERT INTO T1
(V1, V2)
COMMIT EVERY X
AS
SELECT (V1, V2) FROM T2;
I know how to loop through cursor, but I'm looking for something more streamlined.
我知道如何遍历光标，但我正在寻找更精简的东西。
PL/SQL is fine, but no looping.
Using a SQL Hint is good too.
PL/SQL 很好，但没有循环。
使用 SQL 提示也很好。
If this is just something that oracle can't handle, sadness ensues (Mostly I'm just curious since I have another approach working already).
如果这只是 oracle 无法处理的事情，那么悲伤就会随之而来（我只是很好奇，因为我已经有了另一种方法）。
NOTE: The application has hundreds of billions of records. Millions are created per day. INSERT INTO SELECT doesn't work on such large sets of data. Especially when there are equally large sets running in parallel.
注意：该应用程序有数千亿条记录。每天创造数以百万计。INSERT INTO SELECT 不适用于如此大的数据集。特别是当有同样大的集合并行运行时。
回答by Justin Cave
Oracle doesn't allow a statement to have interim commits, no. Doing so would violate the basic properties of an ACID database. What happens if the statement fails on row N? Oracle wouldn't be able to roll back the previously committed rows. It wouldn't know which rows had been processed and which hadn't been processed. So your statement would be partially successful and your database would be left in an unknown state. One of the major benefits of using a relational database is to avoid exactly that outcome.
Oracle 不允许语句具有临时提交，不。这样做会违反 ACID 数据库的基本属性。如果语句在 N 行失败会发生什么？Oracle 将无法回滚先前提交的行。它不知道哪些行已经被处理，哪些没有被处理。因此，您的语句将部分成功，而您的数据库将处于未知状态。使用关系数据库的主要好处之一是完全避免这种结果。
Why do you want to do an interim commit in the first place? That's going to make your code slower and cause you to use more resources. It's going to force you write a bunch of code to make your process restartable (i.e. you'd have to track which rows had been processed and which hadn't so that you could either roll back partially complete changes or restart the process if it fails in the middle). And it's going to make your code much harder to test. There is almost never a good reason to do an interim commit.
为什么首先要进行临时提交？这将使您的代码变慢并导致您使用更多资源。这将迫使您编写一堆代码来使您的流程可重新启动（即您必须跟踪哪些行已被处理，哪些尚未处理，以便您可以回滚部分完成的更改或在失败时重新启动流程在中间）。这将使您的代码更难测试。几乎从来没有一个很好的理由进行临时提交。
回答by Rob van Wijk
Oracle doesn't provide such a SQL construct. Using PL/SQL with a cursor and the FORALL statement can do it for you ... BUT:
Oracle 不提供这样的 SQL 构造。使用带有游标的 PL/SQL 和 FORALL 语句可以为您做到这一点......但是：
It's good that Oracle doesn't provide such sadness. Oracle is an RDBMS that is build upon the ACID principles. The I stands for isolation which, in Oracle, defaults to READ COMMITTED. Meaning that other concurrent transactions can't see your transaction half way through. It prevents other sessions from seeing inconsistent data. One of the cornerstones of using an RDBMS.
幸好 Oracle 没有提供这样的悲伤。Oracle 是一个建立在 ACID 原则之上的 RDBMS。I 代表隔离，在 Oracle 中，默认为 READ COMMITTED。这意味着其他并发事务无法在中途看到您的事务。它可以防止其他会话看到不一致的数据。使用 RDBMS 的基石之一。
Which brings us to the most important question here: Why would you ever want such a construct?
这给我们带来了最重要的问题：你为什么想要这样的结构？
I hope it is not fear of "big" transactions.
我希望这不是对“大”交易的恐惧。
回答by A.B.Cade
I didn't check it, but perhaps with bulk insertsyou can achieve this:
我没有检查它，但也许bulk inserts你可以做到这一点：
DECLARE
TYPE ARRAY IS TABLE OF T2%ROWTYPE;
l_data ARRAY;
x NUMBER;
CURSOR c IS SELECT V1, V2 FROM T2;
BEGIN
OPEN c;
LOOP
FETCH c BULK COLLECT INTO l_data LIMIT x;
FORALL i IN 1..l_data.COUNT
INSERT INTO T1 VALUES l_data(i);
COMMIT;
EXIT WHEN c%NOTFOUND;
END LOOP;
CLOSE c;
END;
But as Justin Cave, WHy ???
但作为贾斯汀凯夫，为什么？？？
