Oracle & Pagination

Question

I have a oracle table with record count of 99896618.

I need to fetch small chunk of data(lets say 100 records) to show it on a web page,(In web world we call it paging). Currently I am using the following query to accomplish that however users are not satisfied with the performance.

SELECT * FROM (select rownum rnum,f.* from  findings f where rownum<90000100 ) 
                    WHERE rnum > 90000000 

Currently it is taking 1 min 22 seconds to get the results. Is there anyway to make it better. I am certainly open for any type of suggestions including modifying the table structure or like adding indexes.

(Just FYI,I am using ASP.NET as server side web technology and ADO.NET as data access layer and silverlight for client side presentation)

Answer 1

Your query will need to count off the first 90M records to get the next 100, so there is hardly a room for improvement.

I don't see an ORDER BY clause in your subquery, but probably you have it. In this case, you may want to create an index on it.

And a question: do your users really click through 900K pages before complaining about performance?

Update:

If you need the latest page, you need to rewrite your ORDER BY column in descending order:

SELECT  *
FROM    (
        SELECT  rownum rnum, f.*
        FROM    findings f
        ORDER BY
                record_ordering_column DESC
        ) 
WHERE   rnum > 900
        AND rownum <= 100

and create an index on record_ordering_column

Note that I mix rownum's from the nested queries to improve performance.

See this article in my blog for more detail:

• Oracle: ROW_NUMBER vs ROWNUM

Answer 2

From one of your comments:

most of the time(around 95% of the time) users are interested in the last(latest) records

In that case, why not show the records in reverse order so that 95% of the time the users are interested in page 1 rather than page 900,000?

If they really then want to see "page 900,000", that means they are interested in data from a long time ago, so allow them to filter data by e.g. date range. Just paging through 100 million rows without any filtering is never going to be performant.