Author's name: Keith_Jamieson
Author's Email: Keih_Jamieson@hotmail.com
Date written: 7 Oct 2003
Oracle version(s): 18.104.22.168.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
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
ORDER BY empno
It is vital that you alias the rownum variable.
SELECT r_no, ename
SELECT rownum r_no,
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