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: Keih_Jamieson@hotmail.com

Date written: 7 Oct 2003

Oracle version(s): 9.2.0.1.0

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

  FROM(

       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

R_NO ENAME

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

3 WARD

4 JONES

5 MARTIN

6 BLAKE

7 CLARK

8 SCOTT

9 KING

 

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