Pagination in Oracle

Always we need to present dataset from Database in application. But the dataset is usually too huge to query all the records. So pagination is needed to improve performance.

Firstly, ROWNUM is involved to implement pagination. ROWNUM is a pseudo-column that returns a column’s position in Oracle database. ROWNUM is evaluated after records are selected from database and before the execution of ORDER BY clause.

The following filter will return no data

         WHERE ROWNUM > x (x<>0)

      WHERE ROWNUM BETWEEN x AND y

However this will work

      WHERE ROWNUM < x

A funny magic shown below ROWNUM will out of numeric order

SQL1:

         SELECT A.ROWNUM, A.rowid FROM A

      ORDER BY A.rowid, A.ROWNUM

 

Secondly, an example for pagination provides the SQL to get the dataset of the specified page.

SQL2:

         DECLARE pageNo int, pageSize int;

      SELECT A.rowid, A.ROWNUM FROM

           (SELECT ROWNUM AS rm, rowid FROM B

WHERE ROWNUM <=  pageNo*pageSize) A

      WHERE a.rm > (pageNo-1)*pageSize;

 

Also the SQL3 below also works

SQL3:

      SELECT A.rowid, A.ROWNUM FROM

           (SELECT ROWNUM AS rm, rowid FROM B) A

      WHERE A.rm > (pageNo-1)*pageSize AND A.rm <= pageNo*pageSize;

At the performance point, better to use SQL2