oracle - 这就是非批量绑定(bind) PL/SQL 代码应如何转换为批量绑定(bind)代码的方式吗？是否有理由放弃 buk 绑定(bind)？

Question

(这都是 Oracle 10g):

CREATE OR REPLACE FUNCTION bar(...)
IS
    v_first_type VARCHAR2(100) ;
    v_second_type VARCHAR2(100);

    CURSOR cur IS SELECT a,b FROM source_table ;
    v_a int;
    v_b char;
BEGIN
    OPEN cur;
    <<l_next>> --10G doesn't have the continue statement.
    LOOP
        FETCH cur INTO v_a, v_b ;
        EXIT WHEN cur%NOTFOUND ;

        --Ignore Record Case: ignore the record entirely
        IF a == -1 THEN
            -- do something
            GOTO l_next ; --10g doesn't have the continue statement.
        ELSE
            -- do something else
            v_first := 'SUCCESS' ;
        END IF;

        -- Transform Case:
        IF b == 'z' THEN
            -- do something
            v_second := 'something';
        ELSE
            -- do something
            v_second := 'something else';
        END IF;


        INSERT INTO report_table VALUES (v_first, v_second);
    END LOOP;
    CLOSE cur;
EXCEPTION 
    ...
END;

我是大学毕业后的第一份工作。我正在查看一些看起来像上面的通用框架的遗留代码(除了它有几百行长并且使用更复杂的处理(不可能有基于集合的解决方案))。

它从一个表中拉取大量行到游标中，遍历游标，对其进行转换，然后将结果插入到报表中。游标不会插入每条记录——如果记录有问题或出于任何原因不喜欢它，它将跳过记录而不插入它(请参阅 GOTO 语句)。

问题 1:插入在循环内一个接一个地发生，而不是在循环外的最后执行 FORALL。

问题 2:游标不使用 BULK COLLECT。

除此之外，还有一个具有游标的存储过程，该游标同样不使用 BULK COLLECT，它在遍历游标中的记录时发出此函数。在循环的每条记录末尾发出一次提交。我在这里写的函数中没有提交。

我想重写代码如下:

CREATE OR REPLACE FUNCTION bar(...)
IS
    CURSOR cur IS SELECT a,b FROM source_table ;

    TYPE t_source IS TABLE OF cur%ROWTYPE INDEX BY PLS_INTEGER;
    TYPE t_report IS TABLE OF destination_table%ROWTYPE INDEX BY PLS_INTEGER;
    v_sources t_source;
    v_reports t_report
    v_report_inx INT := 0; -- To Prevent Sparse Collection
BEGIN
    OPEN cur;
    <<l_next>> --10G doesn't have the continue statement.
    LOOP
        FETCH cur BULK COLLECT INTO v_sources LIMIT 100 ;
        EXIT WHEN v_sources.count = 0 ;

        FOR i IN 1 .. v_sources LOOP
            --Ignore Record Case: ignore the record entirely
            IF v_sources(i).a == -1 THEN
                -- do something
                GOTO l_next ; --10g doesn't have the continue statement.
            ELSE
                -- do something else
                v_reports(v_report_inx).first := 'SUCCESS' ;
            END IF;

            -- Transform Case:
            IF v_sources(i).b == 'z' THEN
                -- do something
                v_reports(v_report_inx).second := 'something';
            ELSE
                -- do something
                v_reports(v_report_inx).second := 'something else';
            END IF;

            v_report_inx := v_report_inx + 1;
        END LOOP;


    END LOOP;

    FORALL i in 1 .. v_reports.count
            INSERT INTO report_table (first, second) VALUES (v_reports(i).first, v_reports(i).v_second);

    CLOSE cur;
EXCEPTION 
    ...
END;

主要变化是 1) 使用 BULK COLLECT 进入关联数组，以及 2) 从不同的关联数组中使用 FORALL。

我有两个问题:

1) 根据我在第一个代码片段中提供的框架，我的修改是否是最好的方法？你会换一种方式吗？

2) 有什么理由让我没有想到哪个会让某人不使用 BULK COLLECT 和 FORALL？也许遗留代码中有一些我还没有实现的复杂处理？该代码最初形成于 2002 年(因此我假设是 8i 或 9i)，但此后一直在更新。 9i had bulk binding . 8i also有批量绑定(bind)。两者都有关联数组。所以我觉得他们没有使用批量绑定(bind)一定是有原因的。

Answer 1

迁移到 bulk collect 的一般想法没有错。批量操作只会最大限度地减少上下文切换和数据库往返的次数。

您的代码只有一个通用的错误。 LIMIT 子句防止批量操作过度使用内存，因此将它与批量收集一起使用是正确的决定。但是 v_reports 不受控制地增长。因此，将批量插入移动到一个循环中，然后清除 v_reports。

修改后的代码有一些不准确之处。请查看下面的代码片段，/**/ 风格的评论是我的。

CREATE OR REPLACE FUNCTION bar(...)
IS
    CURSOR cur IS SELECT a,b FROM source_table ;

    TYPE t_source IS TABLE OF cur%ROWTYPE INDEX BY PLS_INTEGER;
    TYPE t_report IS TABLE OF destination_table%ROWTYPE INDEX BY PLS_INTEGER;
    v_sources t_source;
    v_reports t_report

    /* 1. correct type is same as type of index
       2. There are nothing wrong with sparse collections, but a separate 
           counter which incremented continuously needed for t_report.
    */
    v_report_inx PLS_INTEGER := 0; -- To Prevent Sparse Collection

BEGIN
    OPEN cur;
    <<l_next>> --10G doesn't have the continue statement.
    LOOP
        FETCH cur BULK COLLECT INTO v_sources LIMIT 100 ;

        /* On last step v_sources.count < 100, not exactly 0.
           Also if there are no elements then no processing done, 
           so check at the end of loop.
        EXIT WHEN v_sources.count = 0;
        */

        /* correct way is to loop from 1 to count
          (.last and .first not usable because both is null for empty array)
        */
        FOR i IN 1  .. v_sources.count LOOP

            v_report_inx := v_report_inx + 1;

            --Ignore Record Case: ignore the record entirely
            IF v_sources(i).a = -1 THEN
                -- do something
                GOTO l_next ; --10g doesn't have the continue statement.
            END IF;

            /* No need for ELSE here, just execution continues */
             -- do something else
             v_reports(v_report_inx).first := 'SUCCESS' ;


            -- Transform Case:
            IF v_sources(i).b = 'z' THEN
                -- do something
                v_reports(v_report_inx).second := 'something';
            ELSE
                -- do something
                v_reports(v_report_inx).second := 'something else';
            END IF;

        END LOOP;


        /* Use "indicies of" construct to deal with sparsed collections */
        FORALL i in indices of v_reports
              /* t_report already declared with %ROWTYPE 
                 so just insert entire row, it works faster */
              INSERT INTO report_table VALUES v_reports(i);

        /* Cleanup after insert */
        v_reports.delete;

        /* If number of selected records less than LIMIT then last row reached. */
        EXIT WHEN v_sources.count < 100;

    END LOOP;


    CLOSE cur;
EXCEPTION
    ...
END;

更新

感谢@jonearles。他鼓励我测试在 PL/SQL 中处理游标的不同方法的性能。

以下是 3 000 000 条记录的测试结果。很明显，从普通显式游标迁移到批量收集方法会带来真正的性能提升。
同时，具有批量收集选项的显式游标和正确选择的 LIMIT 总是优于隐式游标，但它们之间的区别在于可接受的范围。

Variant name           | Time (sec)
-------------------------------------
bulk_cursor_limit_500  |  1.26
bulk_cursor_limit_100  |  1.52
bulk_unlimited         |  1.75
implicit_cursor        |  1.83
plain_cursor           | 27.20

下面是测试代码(有限的 SQLFiddle 示例 here)

方案设置

drop table t
/
drop table log_run
/
create table t(a number, b number)
/
insert into t select level, level from dual connect by level <= 3000000
/

create table log_run(id varchar2(30), seconds number);
/

delete log_run
/

单次测试运行

declare
  cursor test_cur is
    select a, b from t;

  test_rec test_cur%rowtype;
  counter    number;

  vStart timestamp;
  vEnd timestamp;
  vTimeFormat varchar2(30) := 'SSSSS.FF9';
begin

  vStart := systimestamp;

  open test_cur;
  loop
    fetch test_cur into test_rec;
    exit when test_cur%notfound;
    counter := counter + 1;
  end loop;
  close test_cur;

  vEnd := systimestamp;
  insert into log_run(id, seconds) 
    values('plain_cursor', 
             to_number(to_char(vEnd,vTimeFormat))
             -
             to_number(to_char(vStart,vTimeFormat)) 
          )
  ;

end;
/

--Implicit cursor
--0.2 seconds
declare
  test_rec   t%rowtype;
  counter    number;

  vStart timestamp;
  vEnd timestamp;
  vTimeFormat varchar2(30) := 'SSSSS.FF9';
begin

  vStart := systimestamp;

  for c_test_rec in (select a, b from t) loop
    test_rec.a := c_test_rec.a;
    test_rec.b := c_test_rec.b;
    counter := counter + 1;
  end loop;

  vEnd := systimestamp;
  insert into log_run(id, seconds) 
    values('implicit_cursor', 
             to_number(to_char(vEnd,vTimeFormat))
             -
             to_number(to_char(vStart,vTimeFormat)) 
          )
  ;

end;
/

declare
  cursor test_cur is
    select a, b from t;

  type t_test_table is table of t%rowtype;

  test_tab   t_test_table;
  counter    number;

  vStart timestamp;
  vEnd timestamp;
  vTimeFormat varchar2(30) := 'SSSSS.FF9';
begin

  vStart := systimestamp;

  open test_cur;
  loop
    fetch test_cur bulk collect into test_tab limit 100;
    for i in 1 .. test_tab.count loop
      counter := counter + 1;
    end loop;

    exit when test_tab.count < 100;
  end loop;

  close test_cur;

  vEnd := systimestamp;
  insert into log_run(id, seconds) 
    values('bulk_cursor_limit_100', 
             to_number(to_char(vEnd,vTimeFormat))
             -
             to_number(to_char(vStart,vTimeFormat)) 
          )
  ;

end;
/


declare
  cursor test_cur is
    select a, b from t;

  type t_test_table is table of t%rowtype;

  test_tab   t_test_table;
  counter    number;

  vStart timestamp;
  vEnd timestamp;
  vTimeFormat varchar2(30) := 'SSSSS.FF9';
begin

  vStart := systimestamp;

  open test_cur;
  loop
    fetch test_cur bulk collect into test_tab limit 500;
    for i in 1 .. test_tab.count loop
      counter := counter + 1;
    end loop;

    exit when test_tab.count < 500;
  end loop;

  close test_cur;

  vEnd := systimestamp;
  insert into log_run(id, seconds) 
    values('bulk_cursor_limit_500', 
             to_number(to_char(vEnd,vTimeFormat))
             -
             to_number(to_char(vStart,vTimeFormat)) 
          )
  ;

end;
/

declare

  type t_test_table is table of t%rowtype;

  test_tab   t_test_table;
  counter    number;

  vStart timestamp;
  vEnd timestamp;
  vTimeFormat varchar2(30) := 'SSSSS.FF9';
begin

  vStart := systimestamp;

  select * bulk collect into test_tab from t;

  for i in 1 .. test_tab.count loop
    counter := counter + 1;
  end loop;

  vEnd := systimestamp;
  insert into log_run(id, seconds) 
    values('bulk_unlimited', 
             to_number(to_char(vEnd,vTimeFormat))
             -
             to_number(to_char(vStart,vTimeFormat)) 
          )
  ;

end;
/

选择平均结果

select * from ( 
  select lr.id, trunc(avg(seconds),2) seconds  
  from log_run lr group by lr.id) 
  order by seconds
)