Difference when using ROWNUM

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

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.