Re: Problem with limitation of result

  • From: Beni Buess <beni.buess@xxxxxxxxxxxx>
  • To: oracle-l@xxxxxxxxxxxxx
  • Date: Tue, 29 Jun 2004 14:56:37 +0200

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@xxxxxxxxxxxx> 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@xxxxxxxxxxxxx
> put 'unsubscribe' in the subject line.
> --
> Archives are at //www.freelists.org/archives/oracle-l/
> FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
> -----------------------------------------------------------------
> 
> ____________________________________________________________
> This message has been checked for all known viruses by Messagelabs.


____________________________________________________________
This message may contain legally privileged or confidential 
information and is therefore addressed to the named persons only. 
The recipient should inform the sender and delete this message, 
if he/she is not named as addressee. 
The sender disclaims any and all liability for the integrity 
and punctuality of this message. 
The sender has activated an automatic virus scanning by 
Messagelabs, but does not guarantee the virus free 
transmission of this message.
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Other related posts: