Преимущества FORALL vs INSERT

Question

В некоторых частях пакета используется insert into select from, а в некоторых - bulk collect + forall insert.

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

Answer 1

Главное отличие - это разные контексты (интерпретаторы) выполнения:

FORALL выполняется в PL/SQL, он передаёт в SQL INSERT INTO ... VALUES (...), т.е. одиночную вставку и матрицу значений переменных связывания для пакета выполнения.
INSERT INTO ... SELECT выполняется чисто в SQL контексте.

На небольших наборах данных разницы в производительности не будет или она будет незначительной. Если данных много, то, как не трудно догадаться, выполнение без переключения контекста будет производительней и для массовой загрузки таблиц как правило используется INSERT INTO ... SELECT.

Для чего же тогда нужен FORALL? INSERT INTO ... SELECT накладывает ряд ограничений, и самое первое из них - при такой вставке невозможно получить автогенерированный первичный ключ:

declare
    nl numlist; 
begin
    insert into t1 (memo)  
        select 'memo'||rownum from dual connect by level<=3
    returning id bulk collect into nl;
end;
/
Error report -
ORA-06550: line 6, column 5:
PL/SQL: ORA-00933: SQL command not properly ended

Это значит, что если нужно в одной транзакции сразу вставлять записи с внешним ключом, то без FORALL не обойтись:

declare
    nl1 numlist := numlist (10,20,30); 
    nl2 numlist; 
begin
    forall i in 1..nl1.count
        insert into t1 (memo) values ('memo'||nl1(i))  
    returning id bulk collect into nl2;
    forall i in 1..nl2.count
        insert into t2 values (nl2(i), 'child'||nl1(i));
end;
/

Результат:

select * from t1 join t2 on t2.t1id = t1.id;

        ID MEMO           T1ID MEMO    
---------- -------- ---------- --------
        19 memo10           19 child10 
        20 memo20           20 child20 
        21 memo30           21 child30

FORALL может также быть приемлимым решением и в других случаях, например, более эффективная вставка с игнорированием ошибок по сравнению с LOG ERRORS.

Подготовка схемы для примеров:

create table t1 (id int generated always as identity primary key, memo varchar2 (96))
/
create table t2 (t1id int not null references t1 (id), memo varchar2 (96)) 
/
create or replace type numlist is table of int
/

БЛОГ НА HUSL

Преимущества FORALL vs INSERT

Ответы (1 шт):