Массовая вставка в базу данных Oracle: что лучше: для цикла курсора или простого выбора?

Ответ 1

Я бы рекомендовал вариант Select, потому что курсоры занимают больше времени.
Также использование Select намного проще понять для тех, кто должен изменить ваш запрос

Ответ 2

Общее правило большого пальца: если вы можете сделать это, используя единую инструкцию SQL вместо PL/SQL, вам нужно. Обычно это будет более эффективно.

Однако, если вам нужно добавить дополнительную процедурную логику (по какой-то причине), вам может понадобиться использовать PL/SQL, но вам следует использовать массовые операции вместо обработки по строкам. (Примечание: в Oracle 10g и более поздних версиях цикл FOR автоматически будет использовать BULK COLLECT для извлечения по 100 строк за раз, однако ваш оператор insert будет по-прежнему выполняться по строкам).

например.

DECLARE
   TYPE tA IS TABLE OF FOO.A%TYPE INDEX BY PLS_INTEGER;
   TYPE tB IS TABLE OF FOO.B%TYPE INDEX BY PLS_INTEGER;
   TYPE tC IS TABLE OF FOO.C%TYPE INDEX BY PLS_INTEGER;
   rA tA;
   rB tB;
   rC tC;
BEGIN
   SELECT * BULK COLLECT INTO rA, rB, rC FROM FOO;
   -- (do some procedural logic on the data?)
   FORALL i IN rA.FIRST..rA.LAST
      INSERT INTO BAR(A,
                      B,
                      C)
      VALUES(rA(i),
             rB(i),
             rC(i));
END;

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

Кроме того, если объем данных очень велик, можно изменить код для обработки данных партиями.

Ответ 3

Если сегмент сегментов отката/отмены может соответствовать размеру транзакции, тогда вариант 2 лучше. Вариант 1 полезен, если у вас нет требуемой способности к откату, и вам нужно разбить большую вставку на более мелкие коммиты, чтобы вы не получали слишком малые ошибки откат/отмена сегмента.

Ответ 4

Простая вставка/выбор, как ваш второй вариант, гораздо предпочтительнее. Для каждой вставки в первом варианте вам нужен контекстный переключатель из pl/sql в sql. Запустите каждый с trace/tkprof и просмотрите результаты.

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

Ответ 5

Я думаю, что в этом вопросе отсутствует одна важная информация.

Сколько записей вы введете?

• Если от 1 до. 10.000, тогда вы должны использовать инструкцию SQL (как они сказали, ее легко понять и ее легко написать).
• Если с. 10.000 до 100.000, то вы должны использовать курсор, но вы должны добавить логику для фиксации на каждые 10.000 записей.
• Если с. От 100 000 до миллионов, тогда вы должны использовать массовый сбор для лучшей производительности.

Ответ 6

Как вы можете видеть, прочитав другие ответы, есть много доступных вариантов. Если вы просто делаете < 10k строк, вы должны пойти со вторым вариантом.

Короче говоря, при приближении > 10k весь путь сказать < 100k. Это своего рода серая область. Многие старые деды будут лаять в больших сегментах отката. Но, честно говоря, аппаратное и программное обеспечение добилось удивительного прогресса, когда вы можете уйти с вариантом 2 для множества записей, если вы только запускаете код несколько раз. В противном случае вы должны, вероятно, совершить каждые строки размером 1k-10k. Вот фрагмент, который я использую. Мне нравится, потому что он короткий, и мне не нужно объявлять курсор. Кроме того, у него есть преимущества массового сбора и forall.

begin
    for r in (select rownum rn, t.* from foo t) loop
        insert into bar (A,B,C) values (r.A,r.B,r.C);
        if mod(rn,1000)=0 then
            commit;
        end if;
    end;
    commit;
end;

Я нашел эту ссылку на сайте oracle, которая более подробно иллюстрирует параметры.

Ответ 7

Вы можете использовать:

Bullk собирает вместе с FOR ALL, который называется Bulk binding.

Так как оператор PL/SQL forall быстрее работает на скорости в 30 раз для простых табличных вставок.

BULK_COLLECT и Oracle FORALL вместе эти две функции известны как "Bulk Binding". Bulk Binds - это метод PL/SQL, в котором вместо нескольких отдельных операторов SELECT, INSERT, UPDATE или DELETE выполняются для извлечения или хранения данных внутри таблицы, все операции выполняются сразу, в массовом порядке. Это позволяет избежать переключения контекста, когда механизм PL/SQL должен перейти к движку SQL, затем вернуться к движку PL/SQL и т.д., Когда вы индивидуально обращаетесь к строкам по одному. Чтобы выполнять массовые привязки с инструкциями INSERT, UPDATE и DELETE, вы заключаете оператор SQL в оператор PL/SQL FORALL. Чтобы выполнять массовые привязки с операторами SELECT, вы включаете предложение BULK COLLECT в инструкцию SELECT вместо использования INTO.

Это улучшает производительность.