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. |
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
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
