# Deep Dive — FETCH FIRST

> Top-N in one clause — and the optimizer knows it can stop after N.

Used in: [Chapter 1 — SQL First](../course-plan.md), [Chapter 8 — Locking](../course-plan.md) · Appendix entry: [FETCH FIRST](../appendix.md#fetch-first) · Related: [FOR UPDATE](for-update.md), [BULK COLLECT](bulk-collect.md), [EXISTS](exists.md)

---

## 1. What It Is and When

The row-limiting clause (12c+): `OFFSET n ROWS FETCH FIRST m ROWS ONLY`. It replaces the pre-12c `ROWNUM` inline-view idiom and the `ROW_NUMBER()` analytic for top-N and pagination. Internally Oracle rewrites it to a `ROW_NUMBER() OVER (ORDER BY ...)` window — which is why it inherits the restrictions of an inline view.

Use it for: top-N reports, "the last 10 orders", capped batches, pagination. Do **not** use it with `FOR UPDATE` — that is what `ROWNUM` is still for.

## 2. Syntax

```sql
SELECT ... FROM ... [WHERE ...] [ORDER BY ...]
[OFFSET <offset> { ROW | ROWS }]
[FETCH { FIRST | NEXT } [<count> | <percent> PERCENT] { ROW | ROWS } { ONLY | WITH TIES }];
```

- `ONLY` — exactly N rows. `WITH TIES` — plus every row equal to the Nth on the `ORDER BY` keys (needs an `ORDER BY`).
- `<count>` and `<offset>` can be bind variables or expressions.
- `PERCENT` requires reading the whole result to know the total.
- Pre-12c equivalents:

```sql
SELECT * FROM (SELECT ... ORDER BY order_date DESC) WHERE ROWNUM <= 10;          -- top-N

SELECT * FROM (SELECT t.*, ROW_NUMBER() OVER (ORDER BY order_date DESC) rn        -- pagination
               FROM   orders t WHERE customer_id = :id)
WHERE  rn BETWEEN 21 AND 30;
```

## 3. Examples on the Course Schema

### 3a. Last 10 orders of a customer

```sql
SELECT order_id, order_date, amount
FROM   orders
WHERE  customer_id = :id
ORDER  BY order_date DESC
FETCH FIRST 10 ROWS ONLY;
```

With an index on `(customer_id, order_date)` the plan is `INDEX RANGE SCAN DESCENDING` + `WINDOW NOSORT STOPKEY` — it reads 10 index entries and stops. Without the index: a full sort of every order of that customer, then the top 10.

### 3b. WITH TIES — everyone who ties for the last place

```sql
SELECT customer_id, SUM(amount) AS total
FROM   orders
WHERE  order_date >= DATE '2026-01-01'
GROUP  BY customer_id
ORDER  BY total DESC
FETCH FIRST 100 ROWS WITH TIES;
```

### 3c. Capping a batch in PL/SQL

```sql
DECLARE
  TYPE t_ids IS TABLE OF orders.order_id%TYPE;
  l_ids t_ids;
BEGIN
  SELECT order_id BULK COLLECT INTO l_ids
  FROM   orders
  WHERE  status = 'NEW'
  ORDER  BY order_date
  FETCH FIRST :batch_size ROWS ONLY;
  -- process l_ids
END;
```

Bind variables are fine in the clause. This is the read-only version of a batch; the locking version must use `ROWNUM` (see 3e).

### 3d. Pagination — OFFSET is a trap on large tables

```sql
-- page 1000 of 50 rows: reads and discards 49,950 rows first
SELECT order_id, order_date, amount
FROM   orders
WHERE  customer_id = :id
ORDER  BY order_date DESC, order_id DESC
OFFSET 49950 ROWS FETCH NEXT 50 ROWS ONLY;

-- keyset pagination: remember the last row seen, always reads exactly 50
SELECT order_id, order_date, amount
FROM   orders
WHERE  customer_id = :id
AND    (order_date, order_id) < ((:last_date, :last_id))      -- row-value comparison
ORDER  BY order_date DESC, order_id DESC
FETCH FIRST 50 ROWS ONLY;
```

Oracle does not support the `(a, b) < (x, y)` row comparison directly — write it out: `order_date < :last_date OR (order_date = :last_date AND order_id < :last_id)`. Every page costs the same with keyset pagination; with `OFFSET` the cost grows with the page number.

### 3e. Where FETCH FIRST cannot go — FOR UPDATE

```sql
-- ORA-02014: cannot select FOR UPDATE from view with DISTINCT, GROUP BY, etc.
SELECT order_id FROM orders WHERE status = 'NEW'
FETCH FIRST 1000 ROWS ONLY FOR UPDATE SKIP LOCKED;

-- the working form
SELECT order_id BULK COLLECT INTO l_ids
FROM   orders
WHERE  status = 'NEW' AND ROWNUM <= 1000
FOR UPDATE SKIP LOCKED;
```

See [FOR UPDATE](for-update.md).

## 4. What Breaks at Scale

- **Top-N without a supporting index** = sort the whole candidate set. On 40M rows of a partition that is a multi-GB sort, possibly to TEMP. The index that matches the `ORDER BY` turns it into a stopkey scan.
- **`OFFSET n`** always materializes and discards `n` rows. Page 10,000 of a report reads 500,000 rows to return 50. Use keyset pagination for anything a user can scroll far into.
- **`PERCENT`** reads everything to compute the count — never a stopkey.
- **`ORDER BY` on a non-unique key** makes the result non-deterministic between executions (rows with equal keys can swap). Add a unique tiebreaker (`order_id`) or use `WITH TIES`.

## 5. Pitfalls

1. No `ORDER BY` — you get "some" N rows, different each run.
2. `ORA-02014` with `FOR UPDATE` — use `ROWNUM`.
3. `OFFSET` for deep pagination — quadratic total cost as users page.
4. `FETCH FIRST` inside a view that is then joined — the limit applies to the view's result, not to the join; often not what was intended.
5. `ROWNUM <= n` **without** an inline view and `ORDER BY` inside it returns the first N rows *before* sorting — the classic pre-12c bug. `FETCH FIRST` does not have it.

## 6. Related

- [FOR UPDATE](for-update.md) — the `ROWNUM` form for locking batches.
- [BULK COLLECT](bulk-collect.md) — capped fetches into collections.
- [EXISTS](exists.md) — when N = 1 and you only need yes/no.