BULK COLLECT и FORALL

Oracle использует два механизма для обработки кода PL/SQL. Весь процедурный код обрабатывается механизмом PL/SQL, в то время как весь SQL обрабатывается исполнителем операторов SQL или механизмом SQL.

BULK COLLECT уменьшает переключение контекста между SQL и механизмом PL / SQL и позволяет механизму SQL получать записи сразу.

 

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

Некоторые правила и ограничения, связанные с использованием секции BULK COLLECT:

• До выхода Oracle9i секция BULK COLLECT могла использоваться только в статических командах SQL. Последующие версии поддерживают ее применение и в динамическом SQL.
• Поскольку BULK COLLECT извлекает запись в BULK, предложение INTO всегда должно содержать переменную типа коллекции. 
• Ключевые слова BULK COLLECT могут использоваться только в секциях SELECT INTO, FETCH INTO и RETURNING INTO.  Синтаксис:
   
  SELECT <columnl> BULK COLLECT INTO bulk_varaible FROM <table name>;

   

  FETCH <cursor_name> BULK COLLECT INTO <bulk_varaible >;
  • Ядро SQL автоматически инициализирует и расширяет коллекции, заданные в секции BULK COLLECT. Заполнение начинается с индекса 1, далее элементы вставляются последовательно (без пропусков), с заменой определенных ранее элементов.
  •  Команда select...bulk collect не выдает исключение no_data_found, если при выборке не получено ни одной строки. Наличие данных проверяется по содержимому коллекции.

             Например, через блок          

      IF collection IS NULL OR collection.COUNT = 0 THEN... ELSE... END IF;

  •  Если запрос не вернул ни одной строки, метод COUNT коллекции возвращает 0.

   Создадим таблицу и наполним ее данными:

  create table accounts
  (id number generated always as identity,
  acc number,
  amount number);
   
  alter table accounts add constraint pk_accid primary key (id);
   
  begin
   
  insert into accounts
  (acc, amount)
   
  select 0 + level as code, round(dbms_random.value*100,2) as amount
  from dual
  connect by level <= 1000000;
  commit;
   
  end;

  Сравним  время, необходимое для заполнения коллекции вручную и с использованием массовой привязки (bulk bind).

   
  DECLARE
    TYPE t_bulk_collect_accounts IS TABLE OF accounts%ROWTYPE;
   
    l_tab    t_bulk_collect_accounts := t_bulk_collect_accounts();
    l_start  NUMBER;
   
  BEGIN
    -- Time a regular population.
    l_start := DBMS_UTILITY.get_time;
   
    FOR cur_rec IN (SELECT *
                    FROM   accounts)
    LOOP
   
      l_tab.extend;
      l_tab(l_tab.last) := cur_rec;
   
    END LOOP;
   
    DBMS_OUTPUT.put_line('Regular (' || l_tab.count || ' rows): ' ||
                         (DBMS_UTILITY.get_time - l_start));
   
    -- Time bulk population. 
    l_start := DBMS_UTILITY.get_time;
   
    SELECT *
    BULK COLLECT INTO l_tab
    FROM   accounts;
   
    DBMS_OUTPUT.put_line('Bulk    (' || l_tab.count || ' rows): ' ||
                         (DBMS_UTILITY.get_time - l_start));
  END;
  /
   
  Regular (1000000 rows): 193
  Bulk    (1000000 rows): 76
   
  BULK COLLECT заметно быстрее.
   
  

  Ограничение числа рядов в выборке 

  Функциональность BULK COLLECT одинаково хорошо работает для ассоциативных массивов (индексные таблицы), вложенных таблиц и varrays. В случае ассоциативных массивов индекс всегда будет PLS_INTEGER и заполняется от 1 до N в зависимости от порядка строк.

  Коллекции хранятся в памяти, поэтому выполнение массового сбора из большого запроса может вызвать значительные проблемы с производительностью. Чтобы предотвратить переполнение памяти данными выборки, можно использовать выражение LIMIT или псевдоколонку ROWNUM для ограничения числа записей в выборке. Кроме того возможно использование выражения SAMPLE для получения набора случайных записей.

   

  ROWNUM  

   
  -- Ограничение числа выбираемых записей до 50
  declare
   
   type t_acclist is table of accounts.acc%type;
   tl_list t_acclist;
   
  begin
   
  select acc
   bulk collect into tl_list
   from accounts
  where rownum <= 50;
   
  end;
   
  

  SAMPLE 

  
  
  -- Получение 10% (приблизительно) записей в таблице 
   
  
  declare
   
   type t_acclist is table of accounts.acc%type;
   tl_list t_acclist;
   
  begin
   
  select acc
   bulk collect into tl_list
   from accounts SAMPLE (10);
   
  end;
   

  LIMIT 

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

   
  DECLARE
    TYPE t_bulk_collect_accounts IS TABLE OF accounts%ROWTYPE;
   

    l_tab t_bulk_collect_accounts;

   
    CURSOR c_data IS

      SELECT *

      FROM accounts;
   
  BEGIN
   
    OPEN c_data;
   
    LOOP
   
      FETCH c_data
   
      BULK COLLECT INTO l_tab LIMIT 10000;
   
      EXIT WHEN l_tab.count = 0;
   
      -- Process contents of collection here.
      DBMS_OUTPUT.put_line(l_tab.count || ' rows');
   
    END LOOP;
   
    CLOSE c_data;
   
  END;
  /

  Output:

  10000 rows
  10000 rows
  10000 rows
  ....
  ....
  10000 rows   -- 100 записей
   
   
  Еще один пример:
  
      
  CURSOR allrows_cur IS
     SELECT * FROM accounts;
    
     TYPE t_accounts IS TABLE OF allrows_cur%ROWTYPE INDEX BY BINARY_INTEGER;      
     tl_accounts t_accounts;
    
  BEGIN
    
  
  
     OPEN allrows_cur;
   
     LOOP
   
        FETCH allrows_cur BULK COLLECT INTO tl_accounts LIMIT 100;
   
        /* Обработка данных с перебором содержимого коллекции */
       FOR i IN 1 .. tl_accounts.COUNT
   
        LOOP
          tl_accounts(i).amount := tl_accounts(i).amount * 1.1;  -- увеличим сумму на 10%
        
        END LOOP;
   
        EXIT WHEN allrows_cur%NOTFOUND;
       
     END LOOP;
   
     CLOSE allrows_cur;
    
  END;
  

  Обратите внимание: цикл прерывается по проверке значения allrows_cur%NOTFOUND в конце цикла. При выборке данных по одной строке этот код обычно размещается сразу же за командой FETCH. 

  С конструкцией BULK COLLECT так поступать не стоит, потому что при достижении последнего набора строк курсор будет исчерпан (а %NOTFOUND вернет TRUE), но при этом в коллекции останутся элементы, которые необходимо обработать. Нужно проверить либо атрибут %NOTFOUND в конце цикла, либо содержимое коллекции непосредственно после выборки:

  LOOP
     FETCH allrows_cur BULK COLLECT INTO tl_accounts LIMIT 100;
     EXIT WHEN tl_accounts.COUNT = 0;
  

  Недостаток этого решения (EXIT WHEN tl_accounts.COUNT = 0) заключается в выполнении дополнительной выборки, не возвращающей строк (по сравнению с проверкой %NOTFOUND в конце тела цикла). 
  
  

  FETCH ROWS

   
  В Oracle 12c появилась возможность использования секции FETCH FIRST/NEXT ROWS для ограничения количества строк, возвращаемых выборкой с BULK COLLECT.
   
  declare
   
   type t_acclist is table of accounts.acc%type;
   tl_list t_acclist;
   
  begin
   
  select acc
   bulk collect into tl_list
   from accounts
   fetch first 50 rows only;
   
  end; 
  
  

  RETURNING INTO

   
  BULK COLLECT используется в условии RETURNING INTO  выражений INSERT, UPDATE, DELETE. Секция RETURNING позволяет получить информацию из команды DML. Таким образом, благодаря RETURNING можно обойтись без дополнительных запросов к базе данных для определения результата только что завершенной операции DML.

  Например: 

  DECLARE
   
   TYPE t_acclist IS TABLE OF accounts.acc%TYPE;
   tlacc_list t_acclist;
   
   TYPE t_amountlist IS TABLE OF accounts.amount%TYPE;
   tlam_list t_amountlist;
   
  BEGIN
   
   DELETE FROM accounts WHERE id < 100
   RETURNING acc, amount BULK COLLECT INTO tlacc_list, tlam_list;
   
   DBMS_OUTPUT.PUT_LINE('Deleted ' || SQL%ROWCOUNT || ' rows');
   
   FOR i IN 1..tlacc_list.count
    
     LOOP
      
      DBMS_OUTPUT.PUT_LINE('Accounts:' || tlacc_list(i) || ': ' || tlam_list(i));
     
     END LOOP;
   
  END;
  /
  
  
  Output:

   
  Deleted 99 rows
   
  Accounts:1: 80.78
  Accounts:2: 85.76
  Accounts:3: 63.72
  ....
  Accounts:98: 55.98
  Accounts:99: 93.2
   

  FOR LOOP vs BULK COLLECT 

   
  Начиная с Oracle 10g оптимизатор преобразует курсор FOR LOOP в BULK COLLECT с размером массива 100. Сравним скорость обычного курсора FOR LOOP с BULK COLLECT с использованием различных размеров массивов.
   
  DECLARE
    TYPE t_bulk_collect_accounts IS TABLE OF accounts%ROWTYPE;
   
    l_tab    t_bulk_collect_accounts;
   
    CURSOR c_data IS
      SELECT *
        FROM accounts;
   
    l_start  NUMBER;
   
  BEGIN
    -- Time a regular cursor for loop.
    l_start := DBMS_UTILITY.get_time;
   
    FOR cur_rec IN (SELECT *
                    FROM accounts)
    LOOP
      NULL;
    END LOOP;
   
    DBMS_OUTPUT.put_line('Regular  : ' ||
                         (DBMS_UTILITY.get_time - l_start));
   
    -- Time bulk with LIMIT 10.
    l_start := DBMS_UTILITY.get_time;
   
    OPEN c_data;
    LOOP
      FETCH c_data
      BULK COLLECT INTO l_tab LIMIT 10;
      EXIT WHEN l_tab.count = 0;
    END LOOP;
    CLOSE c_data;
   
    DBMS_OUTPUT.put_line('LIMIT 10 : ' ||
                         (DBMS_UTILITY.get_time - l_start));
   
    -- Time bulk with LIMIT 100.
    l_start := DBMS_UTILITY.get_time;
   
    OPEN c_data;
    LOOP
      FETCH c_data
      BULK COLLECT INTO l_tab LIMIT 100;
      EXIT WHEN l_tab.count = 0;
    END LOOP;
    CLOSE c_data;
   
    DBMS_OUTPUT.put_line('LIMIT 100: ' ||
                         (DBMS_UTILITY.get_time - l_start));

   

    -- Time bulk with LIMIT 1000.
    l_start := DBMS_UTILITY.get_time;
   
    OPEN c_data;
    LOOP
      FETCH c_data
      BULK COLLECT INTO l_tab LIMIT 1000;
      EXIT WHEN l_tab.count = 0;
    END LOOP;
    CLOSE c_data;
   
    DBMS_OUTPUT.put_line('LIMIT 1000: ' ||
                         (DBMS_UTILITY.get_time - l_start));
  END;
  Output:
   
  Regular  : 81
  LIMIT 10 : 128
  LIMIT 100: 61
  LIMIT 1000: 48
   

  Из этого примера видно, что производительность обычного цикла FOR LOOP приблизительно сравнима с производительностью BULK COLLECT с размером массива 100. Но все же быстрее.

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

   

  FORALL и BULK COLLECT

  
  
  Если FOR автоматически оптмизируется до производительности  BULK COLLECT, то  команды DML в FORALL он не оптимизирует, поэтому нужно явно преобразовать курсорный цикл FOR для использования BULK COLLECT. После этого коллекции, заполненные с BULK COLLECT, используются для управления командой FORALL.

  BULK COLLECT ускоряет выполнение запросов на выборку. FORALL делает то же самое для операций вставки, обновления, удаления и слияния (FORALL с командой MERGE поддерживается начиная с 11g). FORALL приказывает ядру PL/SQL выполнить массовую привязку всех элементов одной или нескольких коллекций перед отправкой команд ядру SQL.

  Хотя команда FORALL выполняет итеративную обработку (то есть перебирает все строки коллекции), она не является циклом FOR, а потому не имеет ни команды LOOP, ни команды END LOOP. Ее синтаксис выглядит так:

  
  
  FORALL индекс IN

     [ нижняя_граница ... верхняя_граница |

        INDICES OF коллекция |

        VALUES OF коллекция

     ]

     [ SAVE EXCEPTIONS ]

     команда_sqL;

  
  

  индекс — целочисленная переменная, неявно объявляемая Oracle;

  нижняя граница — начальное значение индекса (строка или элемент коллекции);

  верхняя граница — конечное значение индекса (строка или элемент коллекции); 

  комaнда_sql — команда SQL, выполняемая для каждого элемента коллекции;

  коллекция — коллекция PL/SQL, используемая для выборки индексов в массиве, упоминаемом в командe sql. 

  конструкции INDICES OF и VALUES OF поддерживаются начиная с OraclelOg. 

  необязательная секция SAVE EXCEPTIONS указывает FORALL на необходимость обработки всех строк данных с сохранением всех возникающих исключений.

  Правила FORALL:

  • Каждый оператор FORALL может содержать только один оператор DML — INSERT, UPDATE, DELETE или MERGE. Если цикл содержит два обновления и удаление, нужно будет написать три оператора FORALL.
  • Команда DML должна содержать ссылки на элементы коллекции, индексируемые в команде FORALL. Область видимости итератора (индекса) ограничивается командой FORALL; ссылаться на нее за пределами цикла нельзя. 
  • Нижняя граница (low_value) и верхняя граница (high_value) не обязаны задавать все множество элементов коллекции.
  • Переменная цикла - итератор (индекс) - не должна объявляться явно. Ядро PL/SQL объявляет ее автоматически с типом PLS_INTEGER.
  • Нижняя граница и верхняя граница должны задавать допустимый диапазон смежных индексов для коллекции, используемой в команде SQL. Коллекции, указанные внутри оператора FORALL, должны быть плотно заполнены. То есть должно быть определено каждое значение индекса между low_value и high_value. Для разреженных коллекций выдается сообщение об ошибке (ORA-22160).
  • Если коллекция не заполнена плотно, нужно использовать синтаксис INDICES OF или VALUES OF в заголовке FORALL.

  Для тестирования BULK COLLECT создадим тестовую таблицу.

  
  
  CREATE TABLE forall_test (
    id           NUMBER(10),
    code         VARCHAR2(10),
    description  VARCHAR2(50));
   
  ALTER TABLE forall_test ADD (
    CONSTRAINT forall_test_pk PRIMARY KEY (id));
   
  ALTER TABLE forall_test ADD (
    CONSTRAINT forall_test_uk UNIQUE (code));

  В тесте ниже сравним время, необходимое для вставки 10 000 строк с использованием обычного FOR LOOP и BULK COLLECT. 

  
  
  DECLARE
    TYPE t_forall_test_tab IS TABLE OF forall_test%ROWTYPE;
   
    l_tab    t_forall_test_tab := t_forall_test_tab();
    l_start  NUMBER;
    l_size   NUMBER            := 10000;
   
  BEGIN
    -- Populate collection.
    FOR i IN 1 .. l_size LOOP
      l_tab.extend;
   
      l_tab(l_tab.last).id          := i;
      l_tab(l_tab.last).code        := TO_CHAR(i);
      l_tab(l_tab.last).description := 'Description: ' || TO_CHAR(i);
    END LOOP;
   
    EXECUTE IMMEDIATE 'TRUNCATE TABLE forall_test';
   
    -- Time regular inserts.
    l_start := DBMS_UTILITY.get_time;
   
    FOR i IN l_tab.first .. l_tab.last LOOP
      INSERT INTO forall_test (id, code, description)
      VALUES (l_tab(i).id, l_tab(i).code, l_tab(i).description);
    END LOOP;
   
    DBMS_OUTPUT.put_line('Normal Inserts: ' ||
                         (DBMS_UTILITY.get_time - l_start));
   
    EXECUTE IMMEDIATE 'TRUNCATE TABLE forall_test';
   
    -- Time bulk inserts. 
    l_start := DBMS_UTILITY.get_time;
   
    FORALL i IN l_tab.first .. l_tab.last
      INSERT INTO forall_test VALUES l_tab(i);
   
    DBMS_OUTPUT.put_line('Bulk Inserts  : ' ||
                         (DBMS_UTILITY.get_time - l_start));
   
    COMMIT;
  END;
  Output:
   
  Normal Inserts: 128
  Bulk Inserts  : 8
   
  BULK COLLECT лидирует со значительным отрывом.
   

  ROW

  Начиная с Oracle9iR2 можно выполнять обновления, используя ROW для определения записей, которые нужно обновить.

  В следующем примере ключевое слово ROW используется при сравнении обычных и массовых обновлений.

  
  

  DECLARE
  
  
    TYPE t_forall_test_tab IS TABLE OF forall_test%ROWTYPE;
    l_tab     t_forall_test_tab := t_forall_test_tab ();
   
    l_start   NUMBER;
    l_size    NUMBER            := 10000;
   
  BEGIN
    -- Populate collections.
    FOR i IN 1 .. l_size LOOP
   
      l_tab.extend;
      l_tab(l_tab.last).id          := i;
      l_tab(l_tab.last).code        := TO_CHAR(i);
      l_tab(l_tab.last).description := 'Description: ' || TO_CHAR(i);
     
    END LOOP;
   
    -- Time regular updates.
    l_start := DBMS_UTILITY.get_time;
   
    FOR i IN l_tab.first .. l_tab.last LOOP
      UPDATE forall_test
      SET    ROW = l_tab(i)
      WHERE  id  = l_tab(i).id;
    END LOOP;
   
    DBMS_OUTPUT.put_line('Normal Updates : ' ||
                         (DBMS_UTILITY.get_time - l_start));
   
    l_start := DBMS_UTILITY.get_time;
   
    -- Time bulk updates.
    FORALL i IN l_tab.first .. l_tab.last
      UPDATE forall_test
      SET    ROW = l_tab(i)
      WHERE  id  = l_tab(i).id;
   
    DBMS_OUTPUT.put_line('Bulk Updates   : ' ||
                         (DBMS_UTILITY.get_time - l_start));
   
    COMMIT;
  END;
  Output:
   
  Normal Updates : 58
  Bulk Updates   : 37
  
  

  UPDATE и ROWID

  Обновление по ROWID можно использовать когда нет первичного или уникального ключа. Но помнить, в каких случаях ROWID может меняться. При обновлении наиболее частый случай - обновление столбца, по которому таблица партиционируется. В таком случае строка перемещается в другую партицию и ROWID изменяется.
  
  

  DECLARE

      TYPE t_code_tab IS TABLE OF forall_test.code%TYPE;
      TYPE t_descr_tab IS TABLE OF forall_test.description%TYPE;
      TYPE t_rwd_tab IS TABLE OF VARCHAR(255);
    
      tl_code_tab  t_code_tab := t_code_tab();
      tl_descrp_tab  t_descr_tab := t_descr_tab();
      tl_rwd_tab  t_rwd_tab := t_rwd_tab();
     
      l_limit    NUMBER  := 100;
     
      CURSOR c_update IS
       SELECT rowid,
              t.code / 100 as code,
              t.description||'0' as description
      FROM forall_test t
      ORDER BY t.rowid;
   
    BEGIN
   
      OPEN c_update;
        LOOP
          FETCH c_update BULK COLLECT
           INTO tl_rwd_tab, tl_code_tab, tl_descrp_tab LIMIT l_limit;
          
           EXIT WHEN tl_rwd_tab.count() = 0;
         
         FORALL i IN tl_rwd_tab.first .. tl_rwd_tab.last
          
           UPDATE forall_test trg
              SET trg.code = tl_code_tab(i),
                  trg.description = tl_descrp_tab(i)
            WHERE ROWID = tl_rwd_tab(i);
        
         COMMIT;
        
       END LOOP;
   
      CLOSE c_update;
      
    EXCEPTION
      WHEN OTHERS THEN
   
         IF c_update%ISOPEN
          THEN
   
           CLOSE c_update;
   
          END IF;
     
      raise_application_error(-20001, SQLERRM);
     
  END;
  
  

  Атрибуты курсоров для FORALL

  Неявные атрибуты курсоров для команд FORALL:

  Name	Description
  SQL%FOUND	Возвращает TRUE, если при последнем выполнении команды SQL была модифицирована хотя бы одна строка
  SQL%NOTFOUND	Возвращает TRUE, если команда DML не модифицировала ни одной строки
  SQL%ROWCOUNT	Возвращает количество строк, модифицированых командой DML
  SQL%ISOPEN	Всегда возвращает FALSE (не используется)
  SQL%BULK_ROWCOUNT	Возвращает псевдоколлекцию, которая сообщает количество строк, обработанных каждой соотвествующей командой SQL, выполненной через FORALL. Обратите внимание: если значение %BULK_ROWCOUNT(j) равно нулю, то атрибуты %FOUND  и %NOTFOUND будут равны TRUE и FALSE соответственно.
  SQL%BULK_EXCEPTIONS	Возвращает псевдоколлекцию с информацией обо всех исключениях, инициированных в команде FORALL с секцией SAVE EXCEPTIONS

  Атрибут курсора SQL% BULK_ROWCOUNT предоставляет детальную информацию о строках, затронутых каждой итерацией оператора FORALL. Каждая строка в управляющей коллекции имеет соответствующую строку в атрибуте курсора SQL% BULK_ROWCOUNT.
  

  
  
  Создадим талицу - копию таблицы all_users.
  
  
  CREATE TABLE bulk_rowcount_test AS
  SELECT *
  FROM all_users;

  Запустим блок, который удаляет пользователей с именами SYS, SYSTEM , DBSNMP и NOBODY.

   
  DECLARE
   
    TYPE t_array_tab IS TABLE OF VARCHAR2(30);
    l_array t_array_tab := t_array_tab('SYS','SYSTEM','DBSNMP','NOBODY');
   
  BEGIN
    -- Perform bulk delete operation.
    FORALL i IN l_array.first .. l_array.last
      DELETE FROM bulk_rowcount_test
      WHERE username = l_array(i);
   
    -- Report affected rows.
    FOR i IN l_array.first .. l_array.last
      LOOP
       
      DBMS_OUTPUT.put_line('Element: ' || RPAD(l_array(i), 15, ' ') ||
                             ' Rows affected: ' || SQL%BULK_ROWCOUNT(i));
     
      END LOOP;
  END;
   
  Output:
  Element: SYS             Rows affected: 1
  Element: SYSTEM          Rows affected: 1
  Element: DBSNMP          Rows affected: 1
  Element: NOBODY          Rows affected: 0
   
  Пользователя NOBODY не было в базе, результат удаления = 0 строк.
  
  
  
  

  RETURNING INTO и FORALL

  
  

  Перепишем тест с использованием RETURNING INTO

  
  
  DECLARE
   
    TYPE t_array_tab IS TABLE OF VARCHAR2(30);
    l_array t_array_tab := t_array_tab('SYS','SYSTEM','DBSNMP','NOBODY');
    type t_userid IS TABLE OF bulk_rowcount_test.user_id%TYPE;
    type t_username IS TABLE OF bulk_rowcount_test.username%TYPE;
    tl_userid t_userid;
    tl_username t_username;
   
  BEGIN
    -- Perform bulk delete operation.
    FORALL i IN l_array.first .. l_array.last
   
      DELETE FROM bulk_rowcount_test
      WHERE username = l_array(i)
      RETURNING user_id, username BULK COLLECT INTO tl_userid, tl_username;
   
      DBMS_OUTPUT.PUT_LINE('Deleted ' || SQL%ROWCOUNT || ' rows:');
     
    -- Report affected rows.
    FOR i IN tl_userid.first .. tl_userid.last
      LOOP
       
      DBMS_OUTPUT.put_line('USER: ' || tl_userid(i) ||' '
       || tl_username(i) || ' have been deleted. Rows affected: ' || SQL%BULK_ROWCOUNT(i));
     
      END LOOP;
  END;

  Output:

  Deleted 3 rows:

  USER: 0 SYS have been deleted. Rows affected: 1
  USER: 9 SYSTEM have been deleted. Rows affected: 1
  USER: 53 DBSNMP have been deleted. Rows affected: 1
  
  

  Значения столбцов, удаленных каждой итерацией, добавляются к ранее полученным значениям коллекций.
  Если бы использовался цикл FOR вместо выражения FORALL, то набор результирующих значений перетирался бы следующим выполнением выражения DELETE.
  Не допускается использование конструкции SELECT ... BULK COLLECT в выражении FORALL.

   

  Поведение ROLLBACK для FORALL

  
  

  Команда FORALL позволяет передать ядру SQL несколько команд SQL. Это означает, что переключение контекста всего одно — но каждая команда выполняется ядром SQL отдельно от других.

  Что случится, если в одной из этих команд SQL произойдет ошибка?

  1. Команда DML, инициировавшая исключение, откатывается от неявной точки сохранения, созданной ядром PL/SQL перед выполнением команды. Изменения во всех строках, модифицированных этой командой, отменяются.
  2. Все предшествующие операции DML в этой команде forall, уже завершенные без ошибок, не отменяются.
  3. Если не принять специальных мер (добавив секцию save exceptions в forall — см. далее), выполнение forall останавливается, и остальные команды вообще не выполняются.

  
  

  FORALL и SAVE EXCEPTION

  
  

  При добавлении в заголовок FORALL секции SAVE EXCEPTIONS, Oracle продолжает обработку даже при возникновении ошибки. Если обработчика исключений нет, вся работа, выполняемая текущей массовой операцией, откатывается. При наличии SAVE EXCEPTIONS исключения сохраняются (или несколько исключений, если ошибок было несколько). При завершении команды DML инициируется исключение ORA-24381. Далее в разделе исключений можно обратиться к псевдоколлекции SQL%BULK_EXCEPTIONS для получения информации об ошибке.

  Воспользуемся ранее созданой таблицей forall_test. Помним, что в этой таблице определен первичный ключ и уникальный ключ. В тесте введем на нарушения уникальности первичного и уникального ключа, и перед вставкой очистим таблицу для подсчета количества записей. 

   
  DECLARE
   TYPE t_tab IS TABLE OF forall_test%ROWTYPE;
    l_tab          t_tab := t_tab();

    l_error_count  NUMBER;

   

    ex_dml_errors EXCEPTION;

   

    PRAGMA EXCEPTION_INIT(ex_dml_errors, -24381);

   

  BEGIN

    -- Fill the collection.

    FOR i IN 1 .. 100 LOOP

      l_tab.extend;

      l_tab(l_tab.last).id := i;

      l_tab(l_tab.last).code        := TO_CHAR(i);

      l_tab(l_tab.last).description := 'Description: ' || TO_CHAR(i);   

     

    END LOOP;

   

    -- Cause a failure -- primary key must have value, not null

    l_tab(50).id := NULL;

    l_tab(51).id := NULL;

    -- Cause a failure -- code must be unique

    l_tab(80).code := '79';

    l_tab(81).code := '79';

   

    EXECUTE IMMEDIATE 'TRUNCATE TABLE forall_test'; 

   

    -- Perform a bulk operation.

    BEGIN

      FORALL i IN l_tab.first .. l_tab.last

        SAVE EXCEPTIONS

       

        INSERT INTO forall_test

        VALUES l_tab(i);

       

    EXCEPTION

     

      WHEN ex_dml_errors THEN

        l_error_count := SQL%BULK_EXCEPTIONS.count;

       

        DBMS_OUTPUT.put_line('Number of failures: ' || l_error_count);

       

        FOR i IN 1 .. l_error_count

        LOOP

   

          DBMS_OUTPUT.put_line('Error: ' || i ||

            ' Array Index: ' || SQL%BULK_EXCEPTIONS(i).error_index ||

            ' Message: ' || SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE));

   

        END LOOP;

       

    END;

   

  END;

  Ошибки вполне ожидаемы.

   

  Output:

   

  Number of failures: 4 

  Error: 1 Array Index: 50 Message: ORA-01400: cannot insert NULL into ()

  Error: 2 Array Index: 51 Message: ORA-01400: cannot insert NULL into ()

  Error: 3 Array Index: 80 Message: ORA-00001: unique constraint (.) violated

  Error: 4 Array Index: 81 Message: ORA-00001: unique constraint (.) violated

  Проверяем количество записей:

   

  select count(*) from forall_test;

   

  COUNT

  96

  Сошлось. 

   
  

  FORALL with Sparse Collections

   

  Коллекция (или массив, или список) называется плотной, если все ее элементы, от первого до последнего, определены и каждому из них присвоено некоторое значение (таковым может быть и NULL). Коллекция считается разреженной, если отдельные ее элементы отсутствуют. Не обязательно определять все элементы коллекции и заполнять ее полностью. Массивы VARRAY всегда являются плотными. Вложенные таблицы первоначально всегда плотные, но по мере удаления некоторых элементов становятся разреженными. Ассоциативные массивы могут быть как разреженными, так и плотными в зависимости от способа их заполнения. Разреженность — это очень ценное свойство, позволяющее добавлять элементы в коллекцию по первичному ключу или другим ключевым данным (например, номеру записи). Таким образом можно задать определенный порядок следования данных в коллекции или значительно ускорить их поиск.

  Если попытаемся использовать синтаксис IN low_value .. high_value с FORALL и в этом диапазоне окажется неопределенное значение индекса, Oracle Database выдаст ошибку
  

  
  

  ORA-22160: element at index [N] does not exist

   

  Чтобы избежать этой ошибки, нужно использовать предложения INDICES OF или VALUES OF.

  Снова вернемся к таблице  forall_test.

   

  INDICES OF

  Удалим после заполнения коллекции 3 элемента.
   
  
  DECLARE

   

    TYPE t_tab IS TABLE OF forall_test%ROWTYPE;

    l_tab          t_tab := t_tab();

     

  BEGIN

    -- Fill the collection.

    FOR i IN 1 .. 1000 LOOP

      l_tab.extend;

      l_tab(l_tab.last).id := i;

      l_tab(l_tab.last).code        := TO_CHAR(i);

      l_tab(l_tab.last).description := 'Description: ' || TO_CHAR(i);   

     

    END LOOP;

   

    -- Delete

    l_tab.delete(201);

    l_tab.delete(306);

    l_tab.delete(407);

   

    EXECUTE IMMEDIATE 'TRUNCATE TABLE forall_test';

   

    -- This would fail due to sparse collection - ORA-22160

    -- FORALL i IN l_tab.first .. l_tab.last

    --  INSERT INTO forall_test VALUES l_tab(i);

   

    -- This works fine with sparse collections.

   

    FORALL i IN INDICES OF l_tab BETWEEN 1 and 500

        INSERT INTO forall_test VALUES l_tab(i);

   

  END;
   

  В FORALL вместо того, чтобы указывать LAST и FIRST указываем INDICES OF. Секция BETWEEN - при необходимости ограничиваем пул индексов. 

  И 497 строк будет успешно вставлено.

  
  

  INDICES OF указывает использовать только те значения индекса, которые определены в l_tab, а не указывать фиксированный диапазон значений. Важны лишь позиции или номера строк, определенных в коллекции, содержимое индексного массива игнорируется. Oracle просто пропустит любые неопределенные значения индекса, и ошибка ORA-22160 не возникнет.

  На практике это используется тогда, когда в несколько коллекций выбираются нужные значения, и в коллеции, которая работает в FORALL в части IN..  могут быть NULL. 

   

  VALUES OF

  VALUES OF почти идентична INDICES OF по смыслу, но применяется тогда, когда важны значения, а не индексы. И INDICES OF не работает для ассоциативных массивов, индексированных строками.
  
  

  В тесте выше подмена INDICES OF на VALUES OF.  выдаст ошибку:
  PLS-00667: Element type of associative array should be pls_integer or binary_integer

  Итак, для   VALUES OF существуют следующие ограничения:

  • Коллеккция должна быть  NESTED TABLE или ASSOCIATIVE ARRAY.
  • Ассоциативные массивы должны быть индексированы  PLS_INTEGER или BINARY_INTEGER.
  • Элементы коллекции должны быть либо PLS_INTEGER, либо BINARY_INTEGER.

  На практике VALUES OF не используется, сложно подобрать случай, когда действительно необходимо создать две коллекции, и по циклу первой коллекции вставить или обновить записи из второй коллекции, вдобавок ассоциативного массива. Если вдруг кто-то знает случаи практического применения - напишите в комментариях.
  
  

  Явное различие можно продемонстироровать в следующем примере:

  Создадим таблицу values_of_test. 
   
  CREATE table values_of_test
  (id number);

   

  Выполним следующий тест:

  DECLARE

    -- объявим тип для первой коллекции

    TYPE t_tab1 IS TABLE OF values_of_test%ROWTYPE;

   

    -- объявим тип для второй коллекции

    TYPE t_tab2 IS TABLE OF BINARY_INTEGER;

   

    -- инициализуем

    l_tab1  t_tab1 := t_tab1();

    l_tab2  t_tab2 := t_tab2();

   

  BEGIN

    -- заполним коллекции

    FOR i IN 1 .. 1000 LOOP

   

      l_tab1.extend;

      l_tab1(l_tab1.last).id := i;

   

      -- во второй коллекции отберем только те записи, значение которых кратно 100,

      -- исходя из количества первой коллекции их должно получиться 10 записей

   

      IF MOD(i, 100) = 0  

        THEN

        l_tab2.extend;

        l_tab2(l_tab2.last) := i;

   

      END IF;

   

    END LOOP;

   

    -- заполним таблицу ЗНАЧЕНИЯМИ из первой коллекции,

    -- которым соотвестствуют ЗНАЧЕНИЯ из второй коллекции

    FORALL i IN VALUES OF l_tab2

      INSERT INTO values_of_test VALUES l_tab1(i);

   

    -- для сравнения заполним таблицу ЗНАЧЕНИЯМИ из первой коллекции,

    -- которым соотвестствуют ИНДЕКСЫ из второй коллекции 

    FORALL i IN INDICES OF l_tab2

      INSERT INTO values_of_test VALUES l_tab1(i);

     

  END;

  Результат: в случае VALUES OF вставили значения, в случае INDICES OF - вставили индексы.

   

  

  
  

  Где это применять на практике сложно сказать, но теоритически работает вот так.

   

  BULK BINDS and Triggers

  Для массовой вставки триггеры уровня оператора срабатывают только в начале и в конце всей массовой операции, а не для каждой строки коллекции.

   

  FORALL & INSERT in parallel

  При попытке выполнить INSERT паралелльно, как в случае ниже, получим ошибку.

  EXECUTE IMMEDIATE 'ALTER SESSION ENABLE PARALLEL DML';

  FORALL i IN 1..t_tab.COUNT

  INSERT /*+ PARALLEL(x) */ INTO table_x x (...)

  SELECT ... FROM table_y y WHERE y.column_z = t_tab(i);

  ORA-12838: cannot read/modify an object after modifying it in parallel

  Эта ошибка возникает естественным образом, поскольку режим по умолчанию такой параллельной вставки - DIRECT PATH.  Невозможно при DIRECT PATH без фиксации снова вставить новую строку. Можно использовать хинт NOAPPEND, но это отключит параллельный режим. Это не баг, это фича.
  
  

   

  FORALL и MERGE                                   

  При необходимости можно использовать FORALL с MERGE.

   

  DECLARE

    TYPE t_forall_test_tab IS TABLE OF forall_test%ROWTYPE;

   

    l_tab    t_forall_test_tab := t_forall_test_tab();

    l_data    t_forall_test_tab := t_forall_test_tab();

    l_size   NUMBER            := 100;

   

  BEGIN

    -- Заполняем коллекцию

    FOR i IN 1 .. l_size 

   

     LOOP

   

      l_tab.extend;

      l_tab(l_tab.last).id          := i;

      l_tab(l_tab.last).code        := TO_CHAR(i);

      l_tab(l_tab.last).description := 'Description: ' || TO_CHAR(i);

   

     END LOOP;

   

    EXECUTE IMMEDIATE 'TRUNCATE TABLE forall_test';

    -- Вставляем первоначальные 100 строк

    FORALL i IN l_tab.first .. l_tab.last

      INSERT INTO forall_test VALUES l_tab(i);

   

    COMMIT;

   

    l_size := 150;

    -- Формируем вторую коллекцию с большим количество записей и измененным кодом

    FOR i IN 1 .. l_size 

    

     LOOP

   

      l_data.extend;

      l_data(l_data.last).id          := i;

      l_data(l_data.last).code        := TO_CHAR(i)||'01';

      l_data(l_data.last).description := 'NEW Description: ' || TO_CHAR(i)||'01';

   

     END LOOP;

   

    -- Сливаем данные из таблицы и второй коллекции

    FORALL i IN l_data.first .. l_data.last

   

    MERGE INTO forall_test trg

    USING (SELECT l_data(i).id as id,

                  l_data(i).code as code, 

                  l_data(i).description as description

            FROM dual

          ) src

          ON (trg.id = src.id)

    WHEN MATCHED THEN

   

     UPDATE SET trg.code = src.code,

                trg.description = src.description

   

    WHEN NOT MATCHED THEN

   

     INSERT (id, code, description)

    VALUES (l_data(i).id,

            l_data(i).code, 

            l_data(i).description);

       

    COMMIT; --  итог 150 строк в таблице

   

  END;
   

  TL;DR

  
  

  BULK COLLECT уменьшает переключение контекста между SQL и механизмом PL / SQL и позволяет механизму SQL получать записи сразу. 

  Поскольку BULK COLLECT извлекает запись в BULK, предложение INTO всегда должно содержать переменную типа коллекции.

  Для ограничения числа записей в выборке можно использовать выражение LIMIT или псевдоколонку ROWNUM, SAMPLE для получения набора случайных записей. C 12c - FETCH ROWS.

  BULK COLLECT используется в условии RETURNING INTO  выражений INSERT, UPDATE, DELETE. Секция RETURNING позволяет получить информацию из команды DML.

  Начиная с Oracle 10g оптимизатор преобразует курсор FOR LOOP в BULK COLLECT с размером массива 100.

  FORALL выполняет итеративную обработку (то есть перебирает все строки коллекции), она не является циклом FOR, а потому не имеет ни команды LOOP, ни команды END LOOP.
  Начиная с Oracle9iR2 можно выполнять обновления, используя ROW для определения записей, которые нужно обновить.

  Обновление по ROWID можно использовать когда нет первичного или уникального ключа. Но помнить, в каких случаях ROWID может меняться.

  Атрибут курсора SQL% BULK_ROWCOUNT предоставляет детальную информацию о строках, затронутых каждой итерацией оператора FORALL. 

  При добавлении в заголовок FORALL секции SAVE EXCEPTIONS, Oracle продолжает обработку даже при возникновении ошибки. 

  Чтобы работать с разреженными коллекциями и избежать ошибки ORA-22160, нужно использовать предложения INDICES OF или VALUES OF. 

  Для массовой вставки триггеры уровня оператора срабатывают только в начале и в конце всей массовой операции, а не для каждой строки коллекции. 

  При необходимости можно использовать FORALL с MERGE.  

   

  Источники:

  BULK COLLECT - Oracle Docs

  FORALL- Oracle Docs 

  Bulk Binds (BULK COLLECT & FORALL) and Record Processing in Oracle

  FORALL Support for Non-Consecutive Indexes (Sparse Collections) 
  

  FORALL и BULK COLLECT - ускоряем запросы в PL/SQL 

  FORALL Insert With Parallel DML Enabled - AskTom