Операторы Pl/SQL Bulk Bind/ Faster Update

У меня проблемы с использованием Bulk Bind в PL/SQL. По сути, я хочу, чтобы таблица (Component) обновляла свое значение поля в зависимости от Component_id и fieldname. Все они передаются как параметры (тип varchar2_nested_table - это эффективно и массив строк, один элемент для каждого оператора обновления, который должен произойти). Так, например, если Component_id = 'Compid1' и fieldname = 'name', тогда fieldvalue должно быть обновлено, чтобы быть 'новым именем компонента'.

Я набрал код ниже в связи с этим http://www.oracle.com/technetwork/issue-archive/o14tech-plsql-l2-091157.html. Код работает, но не быстрее простого цикла, который выполняет обновление для каждого элемента в параметрах IN. Таким образом, если параметры имеют 1000 элементов, то будет выполнено 1000 операторов обновления. Я также понимаю, что не использую BULK COLLECT INTO, но я не думал, что мне это нужно, так как мне не нужно ничего выбирать из базы данных, просто обновить.

На данный момент оба занимают 4-5 секунд на 1000 обновлений. Я предполагаю, что я использую массовое связывание неправильно или у меня неправильное понимание предмета, поскольку в примерах я могу обнаружить, что люди выполняют 50 000 строк за 2 секунды и т. Д. Из того, что я понимаю, FORALL должен повысить производительность за счет уменьшения количества переключений контекста. Я попробовал другой метод, который я нашел онлайн, используя курсоры и массовые привязки, но у меня был тот же результат. Возможно, мои ожидания по производительности слишком велики? Я так не думаю, видя другие результаты. Любая помощь будет принята с благодарностью.

create or replace procedure BulkUpdate(sendSubject_in IN varchar2_nested_table_type,
fieldname_in IN varchar2_nested_table_type,fieldvalue_in IN   varchar2_nested_table_type) is


TYPE component_aat IS TABLE OF component.component_id%TYPE
  INDEX BY PLS_INTEGER;
TYPE fieldname_aat IS TABLE OF component.fieldname%TYPE
  INDEX BY PLS_INTEGER;
TYPE fieldvalue_aat IS TABLE OF component.fieldvalue%TYPE
  INDEX BY PLS_INTEGER;

fieldnames fieldname_aat;
fieldvalues fieldvalue_aat;
approved_components component_aat;


PROCEDURE partition_eligibility
IS
BEGIN
  FOR indx IN sendSubject_in.FIRST .. sendSubject_in.LAST
  LOOP
    approved_components(indx) := sendSubject_in(indx);
    fieldnames(indx):= fieldname_in(indx);
    fieldvalues(indx) := fieldvalue_in(indx);
  END LOOP;
END;


PROCEDURE update_components
IS
BEGIN
  FORALL indx IN approved_components.FIRST .. approved_components.LAST
    UPDATE Component
      SET Fieldvalue = fieldvalues(indx)
      WHERE Component_id = approved_components(indx)
      AND Fieldname = fieldnames(indx);
END;

BEGIN
  partition_eligibility;
  update_components;
END BulkUpdate;
Источник

2 ответа

Решение

Есть что-то еще, я подозреваю, что ваши индивидуальные обновления занимают много времени, возможно, из-за наличия триггеров или неэффективных индексов. (Обратите внимание, что если каждое утверждение стоит дорого в отдельности, использование массовых обновлений не сэкономит вам много времени, поскольку переключение контекста незначительно по сравнению с реальной работой).

Вот моя тестовая установка:

CREATE TABLE Component (
  Component_id NUMBER,
  fieldname    VARCHAR2(100),
  Fieldvalue   VARCHAR2(100),
  CONSTRAINT component_pk PRIMARY KEY (component_id, fieldname)
);

-- insert 1 million rows
INSERT INTO component 
  (SELECT ROWNUM, to_char(MOD(ROWNUM, 100)), dbms_random.string('p', 10) 
     FROM dual 
   CONNECT BY LEVEL <= 1e6);

CREATE OR REPLACE TYPE varchar2_nested_table_type AS TABLE OF VARCHAR2(100);
/

SET SERVEROUTPUT ON SIZE UNLIMITED FORMAT WRAPPED
DECLARE
   l_id    varchar2_nested_table_type;
   l_names varchar2_nested_table_type;
   l_value varchar2_nested_table_type;
   l_time  NUMBER;
BEGIN
   SELECT rownum, to_char(MOD(rownum, 100)), dbms_random.STRING('p', 10) 
     BULK COLLECT INTO l_id, l_names, l_value
     FROM dual
   CONNECT BY LEVEL <= 100000;
   l_time := dbms_utility.get_time;
   BulkUpdate(l_id, l_names, l_value);
   dbms_output.put_line((dbms_utility.get_time - l_time) || ' cs elapsed.');
END;
/

Обновление 100000 строк примерно за 1,5 секунды на ничем не примечательной тестовой машине. Обновление одного и того же набора данных строка за строкой занимает около 4 секунд.

Можете ли вы запустить аналогичный скрипт с недавно созданной таблицей?

Источник

Всякий раз, когда мы отправляем блоки PL/SQL на сервер Oracle, всегда выполняются операторы SQL. Через движок SQL, а также выполняются процедурные инструкции. Через процессуальное заявление исполнителя. Этот исполнитель процедурных операторов доступен в движке PL/SQL, когда мы используем большое количество нагрузки через SQL, операторы PL/SQL всегда сервер oracle выполняет эти операторы отдельно через эти движки.

Этот тип методологии выполнения всегда методология выполнения переключения контента снижает производительность приложения. Чтобы преодолеть эту проблему, oracle ввел процесс "массового связывания" с использованием коллекций, т.е. в этом методе сервер oracle выполняет все операторы SQL за раз. Массовый сбор:

Всякий раз, когда мы используем это предложение автоматически. Сервер Oracle выбирает дату и сохраняет ее в коллекциях. Массовый пункт сбора, используемый в

  1. выберите... в... п
  2. оператор извлечения курсора
  3. DML возвращающие предложения

Также см. PL/SQL Bulk Collect и Bulk Bind

Источник
Другие вопросы по тегам