Oracle & Pagination

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)

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 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

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

Method 3

If you are willing to modify the table I would suggest that you add a rownumber column to the table (using an insert trigger and a sequence to set it) and then you add an index to that column.

Method 4

Do you really need to get the whole row back? As this means that you are not using any indexes.

If you still need to get the whole row. Use the following pattern:

SELECT * FROM findings f1 WHERE f1.rowid IN
   (SELECT  rownum rnum, row_id
      FROM (
            SELECT f.rowid row_id
              FROM findings f
          ORDER BY record_ordering_column
           ) 
     WHERE rownum > 900
   )
WHERE rnum <= 100;

See AskTom

Note: the subtle extra SELECT clause as well as using the ROWID querying.

If you add an index on record_ordering_column, then the paganation will use the index to get a set of ROWIDs. Then only load the blocks that contain the rows identified by their ROWIDs.

This will be better than your current query that will be a full table scan.

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0