Deep Dive — LIMIT

1. What It Is and When

LIMIT caps the number of rows a single FETCH ... BULK COLLECT brings back. It is the difference between "bring me everything" and "bring me the next 10,000". Every bulk loop over a result whose size you do not control must have it.

2. Syntax

FETCH <cursor> BULK COLLECT INTO <collection(s)> LIMIT <n>;

<n> is any positive integer expression — a literal, a constant, a parameter, a package variable. It applies only to FETCH; there is no LIMIT on SELECT ... BULK COLLECT INTO or on EXECUTE IMMEDIATE ... BULK COLLECT INTO.

3. Examples on the Course Schema

3a. The pattern — and the one line people get wrong

DECLARE
  CURSOR c IS SELECT order_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;          -- (1) test the collection, not the cursor
    -- ... process l_rows ...
  END LOOP;
  CLOSE c;
END;

Why (1) matters: after the fetch that returns the last, partial batch (say 3,417 rows), c%NOTFOUND is already TRUE — the cursor knows there is nothing more. EXIT WHEN c%NOTFOUND at that point skips those 3,417 rows. Either test COUNT = 0, or move EXIT WHEN c%NOTFOUND to the end of the loop body, after processing.

3b. Batch size as a parameter

CREATE OR REPLACE PROCEDURE reprice_new_orders (p_batch_size PLS_INTEGER DEFAULT 10000) IS
  CURSOR c IS SELECT order_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 p_batch_size;
    EXIT WHEN l_rows.COUNT = 0;
    FOR i IN 1 .. l_rows.COUNT LOOP
      l_rows(i).amount := pricing_pkg.reprice(l_rows(i).order_id, l_rows(i).amount);
    END LOOP;
    FORALL i IN 1 .. l_rows.COUNT
      UPDATE orders SET amount = l_rows(i).amount WHERE order_id = l_rows(i).order_id;
    COMMIT;
  END LOOP;
  CLOSE c;
END;
/

A parameter lets you tune in production without recompiling — and lets the same procedure run with LIMIT 500 on a small dev box.

3c. Measuring what a batch costs in PGA

DECLARE
  CURSOR c IS SELECT * FROM orders WHERE status = 'NEW';
  TYPE t_rows IS TABLE OF c%ROWTYPE;
  l_rows t_rows;
  l_pga0 NUMBER; l_pga1 NUMBER;
BEGIN
  SELECT value INTO l_pga0 FROM v$mystat m JOIN v$statname n ON n.statistic# = m.statistic#
  WHERE  n.name = 'session pga memory';
  OPEN c;
  FETCH c BULK COLLECT INTO l_rows LIMIT 100000;
  SELECT value INTO l_pga1 FROM v$mystat m JOIN v$statname n ON n.statistic# = m.statistic#
  WHERE  n.name = 'session pga memory';
  CLOSE c;
  DBMS_OUTPUT.PUT_LINE('100K rows of orders%ROWTYPE ~ ' || ROUND((l_pga1 - l_pga0) / 1024 / 1024) || ' MB');
END;

Typical result for a 5-column row: 15–30 MB per 100K rows. Multiply by concurrent sessions running the job.

3d. Choosing the number

Illustrative timings for a 10M-row loop with one FORALL update per batch (numbers vary by row size and hardware; the shape does not):

From 1,000 upward the curve is flat. Go higher only for a reason (fewer commits, fewer FORALL statements), never by default.

4. What Breaks at Scale

• No LIMIT on an unbounded cursor = the whole result in PGA. On orders that is ORA-04030: out of process memory after a few minutes, or ORA-04036 when PGA_AGGREGATE_LIMIT is hit — which can kill other sessions' work too.
• The batch is the commit unit (COMMIT): 10K–50K rows per commit is the sweet spot between undo held and commits issued.
• The batch is the error unit (SAVE EXCEPTIONS): with SAVE EXCEPTIONS a failure is confined to one batch; without it, one bad row rolls back the whole FORALL of that batch.
• Concurrency multiplies memory. 8 parallel workers × 100 MB batches = 800 MB of PGA for one job. Size for the number of workers, not for one session.

5. Pitfalls

1. EXIT WHEN c%NOTFOUND immediately after the fetch — loses the last batch. The single most common bulk bug.
2. LIMIT on SELECT ... BULK COLLECT INTO — syntax error; it is FETCH-only.
3. Batches so large that one batch takes minutes — a crash loses minutes of work; a lock is held for minutes (FOR UPDATE).
4. LIMIT too small (10, 50) "to be safe" — you are back to context-switch overhead. 1,000 is the sane minimum.
5. Assuming the collection is emptied by DELETE between fetches — not needed; BULK COLLECT replaces the contents. Calling DELETE costs nothing either, so do not worry about it.
6. COUNT = 0 test with a collection that was never fetched (cursor failed to open) — the OPEN raises first; no issue in practice.

6. Related

• BULK COLLECT — the fetch itself.
• FORALL — writing the batch back.
• COMMIT — commit per batch.
• SAVE EXCEPTIONS — errors per batch.