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?

I think you have probably got the answer you need, but I find that people new to Oracle often struggle with paging queries, especially since mysql makes it so easy with the LIMIT key word. I'd say this is one of the most common things Oracle beginners do wrong, so I wrote an article about it recently - http://betteratoracle.com/posts/18-limiting-query-results-top-n-and-window-queries — Stephen ODonnell, Aug 16 '11 at 09:47

score 4 · Answer 1 · answered Aug 16 '11 at 03:52

4

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.

answered Aug 16 '11 at 03:52

OMG Ponies

325,700
82
523
502

Thanks you for your answer. My major concern is there any performance differences between those two queries in my questions. Can you have a look at the following link: http://stackoverflow.com/questions/241622/paging-with-oracle It wrap the query twice. – GaryX Aug 16 '11 at 03:58
@GaryX: As I said, the queries provided aren't identical; the second query will return everything in the table. Performance is a concern until you get valid data. – OMG Ponies Aug 16 '11 at 04:03
@OMG Ponies, did you mean to say, "Performance is _not_ a concern until you get valid data"? – Shannon Severance Aug 21 '11 at 06:22

score 4 · Accepted Answer · answered Aug 16 '11 at 03:59

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

Difference when using ROWNUM

2 Answers2