# Deep Dive — LIMIT

> A batch size is a promise about memory: no matter how many rows the cursor returns, this loop never holds more than N.

Used in: [Chapter 2 — Bulk](../course-plan.md) · Appendix entry: [LIMIT](../appendix.md#limit) · Related: [BULK COLLECT](bulk-collect.md), [FORALL](forall.md), [COMMIT](commit.md)

---

## 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

```sql
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

```sql
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

```sql
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

```sql
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):

| LIMIT | Fetches | Runtime | PGA per session |
| --- | --- | --- | --- |
| 1 (row by row) | 10,000,000 | ~55 min | negligible |
| 100 | 100,000 | ~6 min | < 1 MB |
| 1,000 | 10,000 | ~2.5 min | ~1 MB |
| 10,000 | 1,000 | ~2 min | ~10 MB |
| 100,000 | 100 | ~2 min | ~100 MB |
| no LIMIT | 1 | ~2 min or ORA-04030 | ~10 GB |

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](commit.md)): 10K–50K rows per commit is the sweet spot between undo held and commits issued.
- **The batch is the error unit** ([SAVE EXCEPTIONS](save-exceptions.md)): 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](for-update.md)).
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](bulk-collect.md) — the fetch itself.
- [FORALL](forall.md) — writing the batch back.
- [COMMIT](commit.md) — commit per batch.
- [SAVE EXCEPTIONS](save-exceptions.md) — errors per batch.