Так ли должен быть переведен код PL/SQL без массовой привязки в код массовой привязки, и есть ли когда-нибудь причина отказаться от привязки buk?

(это все 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: Вставки происходят одна за другой внутри петли, вместо того, чтобы делать ФОРАЛЛ в конце, вне петли.

Задача 2: Курсор не использует массовый сбор.

Кроме того, существует хранимая процедура, у которой есть курсор, который снова не использует 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 and FORALL? Возможно, какая-то сложная обработка, которую я еще не реализовал в унаследованном коде? Код был первоначально сформирован в 2002 году (поэтому я бы предположил, что 8i или 9i), но с тех пор был обновлен. 9i имел объемную привязку . 8i также имел объемную привязку. У обоих были ассоциативные массивы. Так Что Я чувствую, что должна быть причина, почему они не использовали массовое связывание.

2 3
oracle10g legacy-code plsql

2 ответа:

Нет ничего плохого в общей идее перехода к 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 записей. Очевидно, что переход от простого явного курсора к подходу массового сбора данных дает реальный прирост производительности.
В то же время явный курсор с опцией bulk collect и правильно выбранный предел всегда превосходит неявный курсор, но разница между ними лежит в допустимых границах. 

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 здесь )

Настройка схемы 

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
)
2

Я бы переписал это так, чтобы Гото не использовался (я думаю, что я просто старый нереконструированный структурированный программист в душе :-). Я бы также избавился от явного курсора и использовал курсор для цикла, который, начиная с 10g, часто будет привязан к массовому объему за кулисами. Попробуйте:

CREATE OR REPLACE FUNCTION bar(...)
IS
    v_first_type VARCHAR2(100) ;
    v_second_type VARCHAR2(100);
BEGIN
    <<OUTER_LOOP>>
    FOR aRow In (SELECT A, B FROM SOURCE_TABLE)
    LOOP

      <<INNER_LOOP>>
      LOOP  -- This loop is used to allow us to skip the later INSERT, and
            -- will only be passed through once for each row returned by
            -- the FOR loop.
        --Ignore Record Case: ignore the record entirely
        IF aRow.A == -1 THEN
            -- do something
            EXIT INNER_LOOP;  -- rather than GOTO
        ELSE
            -- do something else
            v_first := 'SUCCESS' ;
        END IF;

        -- Transform Case:
        IF aRow.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);

        EXIT INNER_LOOP;  -- the "loop" is used to allow the INSERT to be
                          -- skipped and thus we don't ever want to go back
                          -- to the top
      END LOOP;  -- INNER_LOOP
    END LOOP;  -- OUTER_LOOP
EXCEPTION 
    ...
END;

Обратите внимание на использование внутреннего цикла с явным выходом, чтобы сохранить поток сверху вниз, позволяя при этом явное управление циклом.

Я также рекомендую запустить этот код под Oracle profiler, чтобы получить понимание того, какие строки кода занимают больше всего времени. Пытаться оптимизировать код, угадывая, где находятся узкие места, - пустая трата времени. До тех пор, пока вы не составите профиль, вы будете гадать - и вы никогда не догадаетесь, как часто я ошибался. :- ) Код проводит свое время в самых отвратительных местах...

Делитесь и наслаждайтесь.

1