I have an query which is done for pagination based on rownum
select * from ( select row_.*, rownum rownum_ from (.. my query ..) ASC) row_ where rownum <= 30) where rownum_ > 20
Actually this pagination query is generated by hibernate ORM java tool.
There is an issue we are getting some records are repeating in multiple pages.
to fix this issue we added order by rowid to my query. But this is hitting performance by 2 secs in 6 lacs records in table.
can any suggestions to fix the pagination issue with out performance hit.