Deep Dive — FETCH FIRST

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

Used in
Chapter 1 — SQL First Chapter 8 — Locking
Appendix entry
FETCH FIRST
Related
FOR UPDATE BULK COLLECT EXISTS

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.

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;

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.

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;

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).

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.

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.

  • 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.

  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.