Implementing dynamic paged queries - POJOs in Action: Developing Enterprise Applications with Lightweight Frameworks - page 417

Oracle has the ROWNUM pseudo column, whose value is the (1-based) position of a
row in the result set. A SQL SELECT statement can use the ROWNUM column in the
WHERE clause to control which rows it returns.
A query can use the ROWNUM in a couple of ways. Let's first look at the simplest
use, which is to return the first N rows selected by a query. For example, here is a
query that returns first 10 rows:

SELECT *
FROM (SELECT O.ORDER_ID, R.NAME, …
FROM PLACED_ORDER O, RESTAURANT R
WHERE O.RESTAURANT_ID = R.RESTAURANT_ID
ORDER BY O.ORDER_EXT_ID
) WHERE ROWNUM < 11
This query nests the original query in SELECT ... WHERE ROWNUM < 11 . Oracle
returns the first 10 rows matched by the query. The application could use a query
like this one to display the first page of a result set.
A more elaborate use of ROWNUM is to select a range of rows. Here is a query that
returns rows 11 through 20:

SELECT *
FROM
(SELECT ROWNUM AS RN, XX.*
FROM
(SELECT O.ORDER_ID, R.NAME, …
FROM PLACED_ORDER O, RESTAURANT R
WHERE O.RESTAURANT_ID = R.RESTAURANT_ID
ORDER BY O.ORDER_EXT_ID
) XX
WHERE ROWNUM < 21)
WHERE RN > 10
The inner query that uses ROWNUM returns the first 20 rows and the outer query
ignores the first 10 rows. An application could use a query like this one to display
all pages of a result set except the first.
Using ROWNUM in a query has several benefits:

An application can ensure that Oracle only returns the rows that it wants.

■

It sometimes enables Oracle to execute the query more efficiently.

■

It reduces the amount of data transferred over the network.

■

However, using ROWNUM in a query can sometimes cause Oracle to execute the
query in a less efficient way, and so you should use it on a case-by-case basis.
In section 11.3.5 you will see that Hibernate and some JDO implementations
provide the option of using ROWNUM -like features.