Paging with Oracle

Something like this should work: From Frans Bouma's Blog

SELECT * FROM
(
    SELECT a.*, rownum r__
    FROM
    (
        SELECT * FROM ORDERS WHERE CustomerID LIKE 'A%'
        ORDER BY OrderDate DESC, ShippingDate DESC
    ) a
    WHERE rownum < ((pageNumber * pageSize) + 1 )
)
WHERE r__ >= (((pageNumber-1) * pageSize) + 1)

Ask Tom on pagination and very, very useful analytic functions.

This is excerpt from that page:

select * from (
    select /*+ first_rows(25) */
     object_id,object_name,
     row_number() over
    (order by object_id) rn
    from all_objects
)
where rn between :n and :m
order by rn;

In the interest of completeness, for people looking for a more modern solution, in Oracle 12c there are some new features including better paging and top handling.

Paging

The paging looks like this:

SELECT *
FROM user
ORDER BY first_name
OFFSET 5 ROWS FETCH NEXT 10 ROWS ONLY;

Top N Records

Getting the top records looks like this:

SELECT *
FROM user
ORDER BY first_name
FETCH FIRST 5 ROWS ONLY

Notice how both the above query examples have ORDER BY clauses. The new commands respect these and are run on the sorted data.

I couldn't find a good Oracle reference page for FETCH or OFFSET but this page has a great overview of these new features.

Performance

As @wweicker points out in the comments below, performance is an issue with the new syntax in 12c. I didn't have a copy of 18c to test if Oracle has since improved it.

Interestingly enough, my actual results were returned slightly quicker the first time I ran the queries on my table (113 million+ rows) for the new method:

• New method: 0.013 seconds.
• Old method: 0.107 seconds.

However, as @wweicker mentioned, the explain plan looks much worse for the new method:

• New method cost: 300,110
• Old method cost: 30

The new syntax caused a full scan of the index on my column, which was the entire cost. Chances are, things get much worse when limiting on unindexed data.

Let's have a look when including a single unindexed column on the previous dataset:

• New method time/cost: 189.55 seconds/998,908
• Old method time/cost: 1.973 seconds/256

Summary: use with caution until Oracle improves this handling. If you have an index to work with, perhaps you can get away with using the new method.

Hopefully I'll have a copy of 18c to play with soon and can update