将迭代的 fetch 替换为 BULK COLLECT [英] Replace iteration's fetch to BULK COLLECT

问题描述

有一个对象类型，表格由该对象组成.所以我们开始:

There is an object type and table consists of that objects. So here we go:

create or replace type lpu.someobj_o as object
(      
  name VARCHAR2(75),     
  enroll_date DATE,
  id NUMBER(12)
)

CREATE OR REPLACE TYPE lpu."SOMEOBJ_T" IS TABLE OF someobj_o;

还有 PL/SQL 函数，它在共同ETL 原则下工作.这是一段代码函数:

There's also PL/SQL function that works in common ETL principle. Here is piece of code function:

  for some_cursor_rec in some_cursor(startTime, recordInterval) loop
  open some_cur2(some_cursor_rec.name, some_cursor_rec.id);
  fetch some_cur2 into some_cursor_rec2;
  if some_cur2%rowcount > 0 then
    loop
      pipe row (
        lpu.someobj_o(
        id => some_cursor_rec2.id, 
        name => some_cursor_rec2.name, 
        enroll_date => some_cursor_rec2.enroll_date
      )
      );
      fetch some_cur2 into some_cursor_rec2;
      exit when some_cur2%notfound;
    end loop;      
  end if;
  close some_cur2;
end loop;

好的，所以问题是小的性能.如何提高完成此功能的速度?我读过 BULK COLLECT 应该可以提高性能.但是我如何在我的情况下使用它?我已经尝试过 BULK COLLECT 但它给了我一个错误，即集合类型是错误的.提前致谢！真心希望得到您的帮助！

Ok, so the issue is small performance. How can I increase speed of completing of this function? I have read that BULK COLLECT should improve performance. But how can I use it in my case? I have tried BULK COLLECT but it gave me error that type of collection is wrong. Thanks in advance! Really hope for your help!

推荐答案

如果您关心性能，那么您应该开始考虑组合您的两个光标.

If performance is your concern, then you should start looking at combining your two cursors.

当前您正在 some_cursor 中执行一次查询，并且您在游标 some_cur2 中执行查询的次数与第一个查询中选择的行的次数相同.这很可能是您的性能瓶颈.

Current you are executing the query in the some_cursor once, and you are executing the query in cursor some_cur2 as many times as there are rows selected in the first query. And that's highly likely your performance bottleneck.

如果您将两个查询合并为一个查询并执行游标 for 循环(只执行一次查询)，那么您将自动一次批量获取 100 行，因此可能不会有真正的需要手动转换成批量获取.

If you combine the two queries to one query and do a cursor for loop (which executes the query only once), then you'll automatically be bulk fetching 100 rows at a time, so there probably won't be a real need to manually convert it to do bulk fetching.

问候，
罗布.

这篇关于将迭代的 fetch 替换为 BULK COLLECT的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！