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.

Views: 32

Reply to This

Replies to This Discussion

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.

Reply to Discussion

RSS

Oracle Jobs in US

© 2022   Created by Maisam Agha.   Powered by

Badges  |  Report an Issue  |  Terms of Service