Oracle BULK FETCH 和 FORALL 插入的性能问题
我正在尝试尽快将记录从一个表复制到另一个表。
目前我有一个与此类似的简单游标循环：
FOR rec IN source_cursor LOOP
INSERT INTO destination (a, b) VALUES (rec.a, rec.b)
END LOOP;
我想将其速度加快到超级快，所以我尝试一些批量操作（批量获取，然后是 FORALL 插入）：
这是我用于批量选择/forall 插入的内容。
DECLARE
TYPE t__event_rows IS TABLE OF _event%ROWTYPE;
v__event_rows t__event_rows;
CURSOR c__events IS
SELECT * FROM _EVENT ORDER BY MESSAGE_ID;
BEGIN
OPEN c__events;
LOOP
FETCH c__events BULK COLLECT INTO v__event_rows LIMIT 10000; -- limit to 10k to avoid out of memory
EXIT WHEN c__events%NOTFOUND;
FORALL i IN 1..v__event_rows.COUNT SAVE EXCEPTIONS
INSERT INTO destinatoin
( col1, col2, a_sequence)
VALUES
( v__event_rows(i).col1, v__event_rows(i).col2, SOMESEQEUENCE.NEXTVAL );
END LOOP;
CLOSE c__events;
END;
我的问题是，到目前为止，我还没有看到性能有任何重大提升。根据我的阅读，它应该快 10 倍到 100 倍。
我是否在某个地方错过了瓶颈？
I am trying to copied records from one table to another as fast as possible.
Currently I have a simple cursor loop similiar to this:
FOR rec IN source_cursor LOOP
INSERT INTO destination (a, b) VALUES (rec.a, rec.b)
END LOOP;
I want to speed it up to be super fast so am trying some BULK operations (a BULK FETCH, then a FORALL insert):
Here is what I have for the bulk select / forall insert.
DECLARE
TYPE t__event_rows IS TABLE OF _event%ROWTYPE;
v__event_rows t__event_rows;
CURSOR c__events IS
SELECT * FROM _EVENT ORDER BY MESSAGE_ID;
BEGIN
OPEN c__events;
LOOP
FETCH c__events BULK COLLECT INTO v__event_rows LIMIT 10000; -- limit to 10k to avoid out of memory
EXIT WHEN c__events%NOTFOUND;
FORALL i IN 1..v__event_rows.COUNT SAVE EXCEPTIONS
INSERT INTO destinatoin
( col1, col2, a_sequence)
VALUES
( v__event_rows(i).col1, v__event_rows(i).col2, SOMESEQEUENCE.NEXTVAL );
END LOOP;
CLOSE c__events;
END;
My problem is that I'm not seeing any big gains in performance so far. From what I read it should be 10x-100x faster.
Am I missing a bottleneck here somewhere?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
与简单的
INSERT+SELECT相比，您的代码的唯一好处是可以保存异常，而且（正如 Justin 指出的那样）您还有一个毫无意义的ORDER BYcode> 这使得它做了很多无意义的工作。无论如何，您没有任何代码可以对保存的异常执行任何操作。我只是将其实现为
INSERT+SELECT。The only benefit your code has over a simple
INSERT+SELECTis that you save exceptions, plus (as Justin points out) you have a pointlessORDER BYwhich is making it do a whole lot of meaningless work. You then don't have any code to do anything with the exceptions that were saved, anyway.I'd just implement it as a
INSERT+SELECT.在编码本身需要之前，您不必使用不必要的循环。
You donot have to use loops unnecessarily until it is required in the coding itself.