Oracle Community Network
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.
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
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.