Deep Dive — BULK COLLECT

One context switch brings back a whole batch of rows, not one. That single change is usually the biggest speed-up a PL/SQL loop ever gets.

Used in
Chapter 2 — Bulk
Appendix entry
BULK COLLECT
Related
LIMIT FORALL CURSOR FOR LOOP ASSOCIATIVE ARRAY OPEN FOR

Every FETCH (and every SELECT INTO) is a round trip between the PL/SQL engine and the SQL engine — a context switch. BULK COLLECT fetches many rows in one switch, straight into a collection. On 500M rows the difference between one switch per row and one per 10,000 rows is the difference between hours and minutes.

It appears in four places:

WhereForm
Implicit querySELECT ... BULK COLLECT INTO <coll>
Explicit cursorFETCH <cur> BULK COLLECT INTO <coll> [LIMIT n]
Dynamic SQLEXECUTE IMMEDIATE '...' BULK COLLECT INTO <coll>
DML returningDELETE ... RETURNING <col> BULK COLLECT INTO <coll>

Use it whenever PL/SQL needs to look at the rows. If it does not — if the logic can live in SQL — use no loop at all (Chapter 1).

SQL 
-- into one collection of records
SELECT <cols> BULK COLLECT INTO <coll_of_records> FROM ... ;

-- into several scalar collections, in select-list order
SELECT <c1>, <c2> BULK COLLECT INTO <coll1>, <coll2> FROM ... ;

-- explicit cursor, batched
FETCH <cursor> BULK COLLECT INTO <coll> [LIMIT <n>];

-- dynamic
EXECUTE IMMEDIATE <sql> BULK COLLECT INTO <coll> [USING <binds>];

-- DML
{INSERT | UPDATE | DELETE} ... RETURNING <cols> BULK COLLECT INTO <coll>;

The target collection can be an associative array, nested table or varray (a varray must be large enough). It is always filled densely from index 1, and its previous contents are discarded.

SQL 
DECLARE
  TYPE t_order_rec IS RECORD (order_id orders.order_id%TYPE, amount orders.amount%TYPE);
  TYPE t_orders    IS TABLE OF t_order_rec;
  l_orders t_orders;
BEGIN
  SELECT order_id, amount BULK COLLECT INTO l_orders
  FROM   orders
  WHERE  customer_id = :id AND status = 'NEW';        -- a few dozen rows per customer

  FOR i IN 1 .. l_orders.COUNT LOOP
    DBMS_OUTPUT.PUT_LINE(l_orders(i).order_id || ': ' || l_orders(i).amount);
  END LOOP;
END;

Note: no NO_DATA_FOUND when the result is empty — l_orders.COUNT = 0, that is all.

SQL 
DECLARE
  TYPE t_ids  IS TABLE OF orders.order_id%TYPE;
  TYPE t_amts IS TABLE OF orders.amount%TYPE;
  l_ids  t_ids;
  l_amts t_amts;
BEGIN
  SELECT order_id, amount BULK COLLECT INTO l_ids, l_amts
  FROM   orders WHERE customer_id = :id;
END;

Records (3a) keep the row together; parallel scalar collections are what older FORALL code needed. Since 11g FORALL handles record fields, so prefer records.

SQL 
DECLARE
  CURSOR c IS SELECT order_id, customer_id, amount FROM orders WHERE status = 'NEW';
  TYPE t_rows IS TABLE OF c%ROWTYPE;
  l_rows t_rows;
BEGIN
  OPEN c;
  LOOP
    FETCH c BULK COLLECT INTO l_rows LIMIT 10000;
    EXIT WHEN l_rows.COUNT = 0;
    -- process l_rows(1 .. COUNT) in memory, then FORALL the changes back
    FORALL i IN 1 .. l_rows.COUNT
      UPDATE orders SET status = 'SEEN' WHERE order_id = l_rows(i).order_id;
    COMMIT;
  END LOOP;
  CLOSE c;
END;

c%ROWTYPE as the element type means adding a column to the cursor never breaks the collection declaration. See LIMIT for the %NOTFOUND trap.

SQL 
DECLARE
  TYPE t_ids IS TABLE OF orders.order_id%TYPE;
  l_deleted t_ids;
BEGIN
  DELETE FROM orders
  WHERE  status = 'CANCELLED' AND order_date < ADD_MONTHS(SYSDATE, -36)
  RETURNING order_id BULK COLLECT INTO l_deleted;

  FORALL i IN 1 .. l_deleted.COUNT
    INSERT INTO orders_purge_log (order_id, purged_at) VALUES (l_deleted(i), SYSTIMESTAMP);
END;

Also works after a FORALL — FORALL ... UPDATE ... RETURNING ... BULK COLLECT INTO collects across all iterations.

SQL 
DECLARE
  TYPE t_ids IS TABLE OF orders.order_id%TYPE;
  l_ids t_ids;
BEGIN
  EXECUTE IMMEDIATE
    'SELECT order_id FROM ' || DBMS_ASSERT.SQL_OBJECT_NAME(:tab) || ' WHERE status = :1'
    BULK COLLECT INTO l_ids
    USING 'NEW';
END;

This form has no LIMIT. For large dynamic results use OPEN FOR + FETCH ... BULK COLLECT ... LIMIT.

  • PGA. SELECT ... BULK COLLECT INTO with no cursor brings everything into session memory. 500M rows × 50 bytes = 25 GB → ORA-04030 or ORA-04036 (PGA_AGGREGATE_LIMIT). Anything whose size you do not know in advance goes through a cursor with LIMIT.
  • The batch is the unit of everything. Fetch size, FORALL size, commit frequency (COMMIT), error scope (SAVE EXCEPTIONS) — all follow the LIMIT. 1,000–10,000 is the range; larger batches stop paying off and start costing memory.
  • Diminishing returns. Switch overhead is fixed per fetch; going from 1 → 100 rows per fetch saves ~99% of it, 100 → 10,000 saves most of the remaining 1%. The cursor FOR loop already gives you 100 for free (CURSOR FOR LOOP) — for read-only loops that may be enough.
  • LOBs. A collection of rows with LOB columns holds LOB locators, not the data; fetching them in bulk is fine, but reading each LOB afterwards is a round trip per row.

  1. NO_DATA_FOUND is not raised on an empty result. Check COUNT = 0.
  2. EXIT WHEN c%NOTFOUND placed right after a FETCH ... LIMIT — drops the last, partial batch. Use EXIT WHEN coll.COUNT = 0, or test %NOTFOUND after the processing.
  3. The collection is overwritten on every fetch, not appended. If you need to accumulate, copy the elements out.
  4. BULK COLLECT INTO a varray with a LIMIT smaller than the result → ORA-22165. Use nested tables or associative arrays.
  5. SELECT ... BULK COLLECT in a loop over a "parent" cursor — the N+1 pattern in bulk clothing. Join instead.
  6. Column order must match the collection order in the multi-collection form (3b). A record type with %ROWTYPE avoids that class of bug.
  7. A collection declared at package level keeps its memory until you DELETE it — see COLLECTION METHODS.