Hi ,
I have a big table which has to be used for pagination.Currently we are using the below functionality
SELECT * FROM (SELECT * FROM code WHERE code_id >100 ORDER BY code_id)
where rownum < 100;
Explaination:In the first iteration we are not passing any where condition from Ui the query will be like below
SELECT * FROM (SELECT * FROM code ORDER BY code_id)
where rownum < 100;
but in the second iteration we will take the maximum id from the result set and passed in the query like below if the maximum code_id is 100 then the query will be like for 100 rows pagination
SELECT * FROM (SELECT * FROM code WHERE code_id >100 ORDER BY code_id)
where rownum < 100;
But there are cavets for the above query like if there are 10 records in the code_id 100 and in the previous iteration if few are selected we not selecting the others in the next iteration as the condition SELECT * FROM code WHERE code_id >100 ORDER BY code_id will be false and missing these values.
For this we have implemented row_number but row_number usage has slow response.
Please suggest any body has any better solutions.
I am using Oracle 11g