OracleConnections

Oracle Community Network

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/span>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.

0 members like this

▶ Reply to This

Replies to This Discussion

Permalink Reply by David McMann on August 21, 2013 at 6:41pm

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;

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.