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