Разница в производительности запросов pl / sql для вставки всей и простой вставки SQL

Некоторые экспериментальные данные для вашей проблемы (Oracle 9.2)

массовый сбор

DECLARE 
  TYPE t_number_table IS TABLE OF NUMBER;
  v_tab t_number_table;
BEGIN
  SELECT ROWNUM
  BULK COLLECT INTO v_tab
  FROM dual
  CONNECT BY LEVEL < 100000;

  FORALL i IN 1..v_tab.COUNT
    INSERT INTO test VALUES (v_tab(i));
END;
/
-- 2.6 sec

вставить

-- test table 
CREATE global TEMPORARY TABLE test (id number)
ON COMMIT preserve ROWS;

BEGIN
  INSERT INTO test
  SELECT ROWNUM FROM dual
  CONNECT BY LEVEL < 100000;
END;
/
-- 1.4 sec

вставка прямого пути http://download.oracle.com/docs/cd/B10500_01/server.920/a96524/c21dlins.htm

BEGIN
  INSERT /*+ append */ INTO test
  SELECT ROWNUM FROM dual
  CONNECT BY LEVEL < 100000;
END;
/
-- 1.2 sec

Вставка в выбор, безусловно, должна быть быстрее. Пропускает накладные расходы на сохранение данных в коллекции в первую очередь.

Это зависит от характера работы, которую вы выполняете, чтобы заполнить промежуточные результаты. Если работа может быть выполнена относительно просто в операторе SELECT для INSERT, это обычно будет работать лучше.

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

Следует очень внимательно отметить одну вещь: план запроса, используемый INSERT INTO x SELECT ..., иногда будет сильно отличаться от того, который используется, когда запрос выполняется сам по себе (например, в явном курсоре PL / SQL). При сравнении производительности нужно это учитывать.

Том Кайт из компании asktomhome fame дал более твердый ответ на этот вопрос. Если вы хотите немного поискать, вы можете найти вопрос и его ответ, который содержит подробные результаты тестирования и объяснения. Он показывает курсор plsql по сравнению с массовым сбором plsql, включая влияние периодической фиксации, по сравнению с вставкой sql в качестве выбора.

Insert as select всегда выигрывает, и разница даже в скромных наборах данных огромна.

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

1) Если вычисления требуют выхода за пределы базы данных Oracle, тогда очевидно, что простая вставка в качестве select не поможет.

2) Если решение требует использования вызовов функций PLSQL, переключение контекста потенциально может убить ваш запрос, и вы можете получить лучшие результаты, если plsql вызывает функции plsql. PLSQl был создан для вызова SQL, но не наоборот. Таким образом, вызов PLSQL из SQL обходится дорого.

3) Если вычисления делают код sql очень трудным для чтения, тогда, даже если он может быть медленнее, решение для массового сбора plsql может быть лучше по этим другим причинам.

Удачи.

Когда мы явно объявляем курсор, oracle выделяет частную рабочую область SQL в нашей оперативной памяти. Если у вас есть оператор выбора, который возвращает несколько строк, он будет скопирован из таблицы или представления в частную рабочую область SQL как ACTIVE SET. Его размер - это количество строк, соответствующих вашим критериям поиска. Когда курсор открыт, ваш указатель будет помещен в первую строку ACTIVE SET. Здесь вы можете выполнить DML. Например, если вы выполните какую-то операцию обновления. Он обновит любые изменения в строках в рабочей области, а не в таблице напрямую. Таким образом, он не использует таблицу каждый раз, когда нам нужно обновить. Он загружается один раз в рабочую область, затем после выполнения операции обновление будет выполнено один раз для всех операций. Это уменьшает передачу данных ввода / вывода между базой данных и пользователем.

Я предлагаю использовать явный курсор PL \ SQL, если вы просто собираетесь выполнить любую операцию DML в частной рабочей области, выделенной для курсора. Это не повлияет на производительность сервера базы данных в часы пик.