Paging with Oracle

I am not as familiar with Oracle as I would like to be. I have some 250k records, and I want to display them 100 per page. Currently I have one stored procedure which retrieves all quarter of a million records to a dataset using a data adapter, and dataset, and the dataadapter.Fill(dataset) method on the results from the stored proc. If I have "Page Number" and "Number of records per page" as integer values I can pass as parameters, what would be the best way to get back just that particular section. Say, if I pass 10 as a page number, and 120 as number of pages, from the select statement it would give me the 1880th through 1200th, or something like that, my math in my head might be off.

I'm doing this in .NET with C#, thought that's not important, if I can get it right on the sql side, then I should be cool.

Update: I was able to use Brian's suggestion, and it is working great. I'd like to work on some optimization, but the pages are coming up in 4 to 5 seconds rather than a minute, and my paging control was able to integrate in very well with my new stored procs.

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

Just want to summarize the answers and comments. There are a number of ways doing a pagination.

Prior to oracle 12c there were no OFFSET/FETCH functionality, so take a look at whitepaper as the @jasonk suggested. It's the most complete article I found about different methods with detailed explanation of advantages and disadvantages. It would take a significant amount of time to copy-paste them here, so I won't do it.

There is also a good article from jooq creators explaining some common caveats with oracle and other databases pagination. jooq's blogpost

Good news, since oracle 12c we have a new OFFSET/FETCH functionality. OracleMagazine 12c new features. Please refer to "Top-N Queries and Pagination"

You may check your oracle version by issuing the following statement

SELECT * FROM V$VERSION

Paging with Oracle

Related

Recent Posts