Oracle Pagination Query: OFFSET FETCH vs ROWNUM (Examples)

To write a pagination query in Oracle 12c or later, sort the rows with ORDER BY and add OFFSET n ROWS FETCH NEXT m ROWS ONLY. For page 3 with 10 rows per page, that is OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY. On Oracle 11g and older, you get the same result by wrapping an ordered subquery and filtering on ROWNUM.

This tutorial shows both methods on a small orders table, with the expected output for every page. You will also see keyset pagination, which stays fast on deep pages, and the mistakes that make pages skip or repeat rows.

How pagination works in Oracle

Pagination means returning a result set one slice at a time: rows 1 to 10, then 11 to 20, and so on. Oracle has no LIMIT keyword in 19c. Instead it gives you three tools.

All of them depend on one thing: a deterministic sort order. A table has no built-in order. If two queries sort the same rows differently, page 2 can show a row you already saw on page 1. The Oracle documentation says this directly for both the row limiting clause and ROW_NUMBER: specify an ORDER BY that gives a deterministic order.

Sample setup

Create this table and insert 12 orders. Two pairs of orders share the same date on purpose, so you can see why the sort needs a tiebreaker.

CREATE TABLE orders (
  order_id      NUMBER        PRIMARY KEY,
  customer_name VARCHAR2(50)  NOT NULL,
  order_date    DATE          NOT NULL,
  amount        NUMBER(10,2)  NOT NULL
);

INSERT INTO orders VALUES (101, 'Asha', DATE '2026-01-05', 1200);
INSERT INTO orders VALUES (102, 'Ravi', DATE '2026-01-06', 450);
INSERT INTO orders VALUES (103, 'Meena', DATE '2026-01-06', 980);
INSERT INTO orders VALUES (104, 'John', DATE '2026-01-08', 300);
INSERT INTO orders VALUES (105, 'Priya', DATE '2026-01-09', 2200);
INSERT INTO orders VALUES (106, 'Arjun', DATE '2026-01-10', 750);
INSERT INTO orders VALUES (107, 'Sara', DATE '2026-01-10', 1500);
INSERT INTO orders VALUES (108, 'Kiran', DATE '2026-01-12', 640);
INSERT INTO orders VALUES (109, 'Deepa', DATE '2026-01-13', 1800);
INSERT INTO orders VALUES (110, 'Vikram', DATE '2026-01-14', 520);
INSERT INTO orders VALUES (111, 'Neha', DATE '2026-01-15', 990);
INSERT INTO orders VALUES (112, 'Rahul', DATE '2026-01-15', 410);

COMMIT;

Every example below shows the newest orders first, sorted by order_date DESC, order_id DESC. The page size is 5, so there are three pages: 5 rows, 5 rows and 2 rows.

Method 1: OFFSET and FETCH NEXT (Oracle 12c and later)

The row limiting clause goes at the very end of the query, after ORDER BY. OFFSET is how many rows to skip. FETCH NEXT is how many rows to return after that.

Page 1: the first 5 rows

SELECT order_id, customer_name, order_date, amount
  FROM orders
 ORDER BY order_date DESC, order_id DESC
 FETCH FIRST 5 ROWS ONLY;

Output (expected output):

You can leave out OFFSET on the first page because it defaults to 0. FIRST and NEXT mean the same thing, as do ROW and ROWS. Use whichever reads better.

Page 2 and page 3

SELECT order_id, customer_name, order_date, amount
  FROM orders
 ORDER BY order_date DESC, order_id DESC
OFFSET 5 ROWS FETCH NEXT 5 ROWS ONLY;

Output (expected output):

SELECT order_id, customer_name, order_date, amount
  FROM orders
 ORDER BY order_date DESC, order_id DESC
OFFSET 10 ROWS FETCH NEXT 5 ROWS ONLY;

Output (expected output):

Page 3 asks for 5 rows but only 2 are left, so Oracle returns 2. That is documented behaviour: if the row count is larger than the rows available after the offset, all available rows are returned. If the offset is equal to or larger than the total row count, the query returns no rows, which is how your application knows it has gone past the last page.

Use bind variables for the page number

The offset and row count can be expressions, so you can calculate them from a page number. The formula is offset = (page_number - 1) * page_size.

SELECT order_id, customer_name, order_date, amount
  FROM orders
 ORDER BY order_date DESC, order_id DESC
OFFSET (:page_no - 1) * :page_size ROWS
 FETCH NEXT :page_size ROWS ONLY;

With binds, Oracle can reuse one cursor for every page instead of hard parsing a new statement for each literal. The documentation also lists how odd inputs behave: a negative offset or row count is treated as 0, a NULL row count returns no rows, and fractions are truncated.

Method 2: ROWNUM pagination (Oracle 11g and older)

Before 12c, the standard pattern used three query levels. You still see it in older applications, so it is worth understanding even if you never write it.

SELECT order_id, customer_name, order_date, amount
  FROM (SELECT a.*, ROWNUM AS rnum
          FROM (SELECT order_id, customer_name, order_date, amount
                  FROM orders
                 ORDER BY order_date DESC, order_id DESC) a
         WHERE ROWNUM <= 10)          -- last row of the page
 WHERE rnum > 5;                      -- last row of the previous page

Output (expected output):

Why it needs three levels

• The inner query sorts the rows. ROWNUM is assigned as rows are selected, before ORDER BY in the same query block, so the sort must happen in a subquery first.
• The middle query numbers the sorted rows and stops at the last row of the page with ROWNUM <= 10. This is the Oracle top-N pattern from the documentation.
• The outer query filters on the alias rnum, which is now a normal column, to drop rows 1 to 5.

You cannot simply write WHERE ROWNUM > 5. The documentation explains why: the first row fetched gets ROWNUM 1 and fails the test, so the next row also becomes ROWNUM 1 and fails too. The query returns no rows at all.

Method 3: ROW_NUMBER() pagination

ROW_NUMBER() numbers rows in the order you give in its OVER clause. Filter on that number in an outer query.

SELECT order_id, customer_name, order_date, amount, rn
  FROM (SELECT o.order_id, o.customer_name, o.order_date, o.amount,
               ROW_NUMBER() OVER (ORDER BY o.order_date DESC,
                                           o.order_id DESC) AS rn
          FROM orders o)
 WHERE rn BETWEEN 6 AND 10
 ORDER BY rn;

Output (expected output):

This is longer than OFFSET FETCH, but it is useful when you want paging inside groups. Add PARTITION BY customer_name to the OVER clause and rn <= 3 gives the first three orders for each customer. The outer ORDER BY rn matters: without it, Oracle does not promise the final rows come back in rn order.

Method 4: keyset pagination for large tables

OFFSET has a hidden cost. To return rows 100,001 to 100,010, Oracle still has to find and throw away the first 100,000 rows in sort order. The deeper the page, the more work each request does.

Keyset pagination (also called seek pagination) avoids this. Instead of a page number, the application remembers the sort key of the last row it showed and asks for rows that come after it.

SELECT order_id, customer_name, order_date, amount
  FROM orders
 WHERE order_date < :last_date
    OR (order_date = :last_date AND order_id < :last_id)
 ORDER BY order_date DESC, order_id DESC
 FETCH FIRST 5 ROWS ONLY;

Page 1 ended with order 108 dated 2026-01-12. Bind :last_date to DATE '2026-01-12' and :last_id to 108.

Output (expected output):

It is the same page 2 as before, but no rows were skipped. With an index on (order_date, order_id), Oracle can start reading at the last position and stop after 5 rows, so page 1,000 costs about the same as page 2.

The trade-off: you cannot jump straight to page 37, only to the next (or, with the comparisons reversed, the previous) page. That fits infinite scroll, "Load more" buttons and batch jobs that walk through a whole table. Write the condition with OR as shown. Unlike PostgreSQL, Oracle does not let you compare two-column row values with <, so the shorter (order_date, order_id) < (:d, :id) form from PostgreSQL tutorials will not work here.

How to return the total row count

Pagination screens often show "Page 2 of 3". You can get the total in the same query with the analytic COUNT(*) OVER ().

SELECT order_id, customer_name, order_date, amount,
       COUNT(*) OVER () AS total_rows
  FROM orders
 ORDER BY order_date DESC, order_id DESC
OFFSET 5 ROWS FETCH NEXT 5 ROWS ONLY;

Every row of page 2 comes back with TOTAL_ROWS = 12, because the analytic function is calculated over the whole result before the rows are limited. Be aware that this forces Oracle to read every matching row, which removes the early-stop benefit. On a large table, a separate cached count, or showing only "Next" without a total, is often cheaper.

Common mistakes

1. Putting ROWNUM and ORDER BY in the same query block

-- Wrong: picks 5 rows first, then sorts only those 5
SELECT order_id, order_date
  FROM orders
 WHERE ROWNUM <= 5
 ORDER BY order_date DESC;

The documentation warns that this may return different rows depending on how Oracle accesses the table. Sort in a subquery first, or use FETCH FIRST, where the ORDER BY is always applied before the row limit.

2. Sorting on a column that is not unique

If you sort only by order_date, orders 106 and 107 (both 2026-01-10) can come back in either order. If they sit on a page boundary, one query may put 107 on page 2 and the next may put it on page 3, so the user sees one order twice and never sees the other. Always end the ORDER BY with a unique column such as the primary key.

3. Passing the page number as the offset

OFFSET :page_no ROWS skips 2 rows for page 2, not 5. Calculate (page_no - 1) * page_size.

4. ORA-00918 on joins

The documentation states that if the select list contains columns with identical names and you use the row limiting clause, Oracle raises ORA-00918 (column ambiguously defined), even when the columns come from different tables. A SELECT * over a join of orders and customers that both have customer_id hits this. Give the duplicate columns unique aliases.

5. Expecting stable pages while data changes

Each page is a separate query. If new orders arrive between page 1 and page 2, OFFSET-based pages shift and a row can appear twice. Keyset pagination is much less affected, because it continues from a fixed key instead of a position.

Performance tips

• Create an index that matches the ORDER BY, for example CREATE INDEX orders_date_id_ix ON orders (order_date, order_id);. Oracle can then read rows in order and stop early instead of sorting the whole table for every page.
• Put filters in the WHERE clause, not in application code. Paging 20 rows out of a filtered set of 500 is far cheaper than paging out of 5 million.
• Use bind variables for the offset and page size so every page shares one cursor.
• For deep pages or full-table walks, switch from OFFSET to keyset pagination.
• Check the plan with DBMS_XPLAN.DISPLAY_CURSOR after running the query. A plan with a full scan and a large sort on every page request is the sign that the index is missing or not usable.

Frequently asked questions

Does Oracle support LIMIT like MySQL?

Oracle 19c has no LIMIT keyword. Use FETCH FIRST n ROWS ONLY on 12c and later, or ROWNUM in a subquery on older versions.

Which Oracle version supports OFFSET FETCH?

The row limiting clause (OFFSET and FETCH FIRST) was added in Oracle Database 12c Release 1 (12.1). It works in 12.2, 18c, 19c and later.

What is the difference between ROWNUM and ROW_NUMBER in Oracle?

ROWNUM is a pseudocolumn assigned as rows are selected, before the ORDER BY of the same query block. ROW_NUMBER() is an analytic function that numbers rows in the order given in its OVER clause, and it can restart per group with PARTITION BY.

Why does WHERE ROWNUM > 10 return no rows?

Every row is tested as ROWNUM 1. Row 1 fails the condition, so the next row becomes ROWNUM 1 and fails as well. Filter on an alias of ROWNUM in an outer query instead, or use OFFSET.

How do I get page N of a query in Oracle?

Use ORDER BY unique_columns OFFSET (N - 1) * size ROWS FETCH NEXT size ROWS ONLY. For example, page 4 with 25 rows per page is OFFSET 75 ROWS FETCH NEXT 25 ROWS ONLY.

Summary

• On 12c and later, page with ORDER BY ... OFFSET n ROWS FETCH NEXT m ROWS ONLY.
• On 11g, sort in a subquery, cap with ROWNUM in the middle, filter the ROWNUM alias outside.
• End every ORDER BY with a unique column so pages never overlap or skip rows.
• Offset = (page number - 1) x page size, passed as bind variables.
• Index the ORDER BY columns so Oracle can stop early.
• Use keyset pagination for deep pages, large tables and infinite scroll.
• Remember the restrictions: no FOR UPDATE, no CURRVAL or NEXTVAL, unique column names.