It is always difficult to handle paging in Oracle as we need to put an inner query with
rownum, and we give a
where condition in the outer query with that pseudo
rownum column.
This is the conventional way of doing it:
select * from (select rownum,fld1,fld2,fld3... from bigtable where <<CONDITION>> ) where rownum between 1 to 100
and we say
rownum between 101 to 200, and so on...
We can rewrite this in the following way, but we need a running number column in our table. Let's say our running number column name is
SYSID. Then, we can write this like:
Select * from bigtable where <<CONDITION>> and SYSID>=1 and rownum between 1 to 100
The next time we take the greatest
sysid from the above return query and execute next step like:
Select * from bigtable where <<CONDITION>> and SYSID>PREVIOUS GREATEST SYSID and rownum between 1 to 100
When we get a
rowcount less than
100, that means we are finished with fetching all the rows.
Limitations
Order By,
Group By clauses will not work with this.
