Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Problem with limitation of result

Re: Problem with limitation of result

From: Stephane Faroult <>
Date: Tue, 29 Jun 2004 13:50:38 +0200
Message-Id: <>



You are *partly* right. If you want to be able to do this, you must put the rownum in the SELECT list of the in-line view (and rename it to something else, such as rn, because it is a reserved word), not at the upper level as you did. ROWNUM is computed 'on the fly'. Imagine that the Oracle engine is like a factory and that each time a row gets out it is stamped with a sequence number, and that your WHERE condition is the final quality control before the exit. You will never see a row labelled '10' if you discard any ofrows 1 to 9 inclusive.

Be also aware that if you want to really keep the work done under control, you should actually put a limit on *both* queries. Betting on the intelligence of the optimiser is always a gamble. If you want to return rowsN to P, you should write :

         select *

         from (select rownum rn, blah


                  where ... 

                      and rownum <= P)     <=== Note 

         where rn between N and P 

Otherwise you have a risk that your inner query returns your thousands of rows (which it will do, in the end) to only display a few ones.

Don't complain about your English, you haven't seen my German.


Stephane Faroult

On Tue, 29 Jun 2004 13:00 , Beni Buess <> sent:

thank you! it works now.
i need the between, because i need sometimes something like "BETWEEN 10 AND 20" so i could not do this using "<". but:
i thought, that i've no control over the rowid given by oracle to every row, so i need the inline view to force new rowid's beginning with 1 (or 0) up to the amount of records fetched by the query, because i need this only for limiting the result because my webapp would not be amused getting thousends of records. and if i would not do this using a subquery, i would just get the records with a rowid - given at the time the record was inserted - which is between 0 and 10 for example. and this would not really do what i want.

am i right?

i hope you've understood that, my english is not the best and it was a bit difficult to explain.

Please see the official ORACLE-L FAQ:

To unsubscribe send email to: put 'unsubscribe' in the subject line.

Archives are at FAQ is at
Received on Tue Jun 29 2004 - 06:48:59 CDT

Original text of this message