The Oracle (tm) Users' Co-Operative FAQ

How do I return a result set in batches, e.g. get rows 1 - 10, then 11-20, then next 10 and so on

Author's name: Keith_Jamieson

Author's Email:

Date written: 7 Oct 2003

Oracle version(s):

Typically, people want to mimic the effects they see on a web page which displays the first 10 results, and then has a button to get the next 10 results, etc.

Back to index of questions

We can solve this problem by using INLINE VIEWS. The technique itself is quite simple, however, deciding whether or not it is appropriate to use depends on your environment and the perceived use. For instance, you almost certainly would not use this mechanism on a datawarehouse. However, you might use it on an OLTP system.

Firstly we have to get the entire result set. Here we use the emp table in the standard SCOTT schema supplied by ORACLE. We then use rownum and we have to alias the rownum

SELECT rownum r_no, ename

  FROM emp


Because Oracle doesn’t guarantee that the rows will be returned in any particular order, if we want to guarantee consistency, we should order the rows by some criteria. Because of this, we need to add the order by column into the SELECT list.


SELECT rownum r_no, ename, empno

  FROM emp

ORDER BY empno


It is vital that you alias the rownum variable. 

SELECT r_no, ename


       SELECT rownum r_no,

              Ename, empno

         FROM emp

     ORDER BY empno


 WHERE r_no between X and Y


In my example X= 3 and Y = 9


---------- ----------









7 rows selected.

If you wish to retrieve the next 10 rows you merely replace X by X+10 and Y by Y+10. To go Backwards, replace X with X – 10 and Y with Y – 10. Before using this technique, try and understand any performance implications. Determine how long will it take if the inner query has to scan 1 Million rows, which then have to be ordered.

Further reading: N/A

Back to top

Back to index of questions