Post on how to do Paging in Oracle

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.