Difference when using ROWNUM

Question

I search for Oracle paging query on the net, and most of them told me to wrap the query twice:

SELECT * 
  FROM (SELECT t.*, ROWNUM rn 
          FROM tableName t 
         WHERE ROWNUM < 200) 
 WHERE rn > 100

Just wondering if I can type it as:

SELECT *, ROWNUM rn 
  FROM tableName t 
 WHERE ROWNUN BETWEEN 100 AND 200

It seems the second one works as well. Is there any (performance) differences between these two query?

Answer 1

The issue is that you are filtering in the same query that the ROWNUM is being generated. Hence the reason you must have a subquery generate the rownum first and then apply the filtering. Why the BETWEEN works fine is probably some nuance of how the engine processes the query, but I would be wary that it might not consistently give you correct results. So it's not a matter of performance as it is a matter of actually getting correct results.

This article explains why you have to put the greater than outside the subquery: http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/pseudocolumns009.htm

SELECT * FROM employees
    WHERE ROWNUM > 1;

"The first row fetched is assigned a ROWNUM of 1 and makes the condition false. The second row to be fetched is now the first row and is also assigned a ROWNUM of 1 and makes the condition false. All rows subsequently fail to satisfy the condition, so no rows are returned."

Answer 2

The proper way to use ROWNUM is:

SELECT x.* 
  FROM (SELECT t.*, 
               ROWNUM rn 
          FROM tableName t) AS x
 WHERE x.rn > 100
   AND x.rn < 200

BETWEEN is inclusive, so the two queries are not identical logic.

For more information on ROWNUM, see this link (includes link to Oracle documentation.