| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Problem with limitation of result
Stephane Faroult wrote:
>
> Beni,
>
> 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.
if i understand that right, i didn't really need the subquery!
so i'm able to simply do:
select rownum rn, blah
from where.... and rn BETWEEN N AND P
i tested that and got exactly the same result as i'm using a subquery. it seems to me that there is no difference since the ROWNUM is stamped to rows at the time they get out. right?
regards
beni
>
> 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
>
> from
>
> 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.
>
> Regards,
>
> Stephane Faroult
>
> On Tue, 29 Jun 2004 13:00 , Beni Buess <beni.buess_at_swisslog.com> 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: http://www.orafaq.com
> ----------------------------------------------------------------
> To unsubscribe send email to: oracle-l-request_at_freelists.org
> put 'unsubscribe' in the subject line.
> --
> Archives are at http://www.freelists.org/archives/oracle-l/
> FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
> -----------------------------------------------------------------
>
> ____________________________________________________________
> This message has been checked for all known viruses by Messagelabs.
-- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------Received on Tue Jun 29 2004 - 07:55:14 CDT
 |
 |