Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: sql quiz

Re: sql quiz

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1998/01/10
Message-ID: <34b87d7d.35345363@inet16>#1/1

On Sat, 10 Jan 1998 11:49:02 GMT, Cezariusz.Marek_at_comarch.pl (Cezariusz Marek) wrote:

[snip]
>>
>>select rnum
>> from ( select rownum rnum
>> from all_objects
>> where rownum <= ( select max(x) from foo )
>> ), foo
>> where rnum = x (+)
>> and x is null
>>/
>>
>>We use the inline view "select rownu rnum ... " to generate a set of numbers
>>1..max(x). We then outer join the original set of numbers in foo to this set,
>>only keeping the rows from this join such that foo didn't have a value.
>>
>>that way, if the number of rows/max value in foo changed, the query would still
>>work. You just need to make sure that the table you use in the inline view
>>(all_objects in the above example) has at least as many rows in it as the max
>>value of X......

>
>And what if I don't have a table with enough number of rows? Is there
>a select, that will return _unlimited_ number of rows (which I could
>safely limit by WHERE ROWNUM < xxx)?
>

No, there is no 'infinite' row generator. In the example below, you must have a table with as many rows as max(x) or it will not work.
>And BTW -> how works WHERE ROWNUM < 5, if a table has for example
>100.000.000 rows? Will the query stop after selecting 4 rows, or will
>it count row numbers up to 100.000.000?
>

It'll stop when it gets enough rows -- so if you have 100,000,000 rows and say where rownum < 5, only 4 rows will be processed and it'll stop.
>In other words, if exists select thet returns unlimited number of rows
>thes is it possible to limit them by ROWNUM, or such the query would
>never end?
>
>Regards,
>
>+--------------------------------------------------------------+
>| Cezariusz Marek                   Cezariusz.Marek_at_ComArch.pl |
>| ComArch Bielsko-Bia³a             tel. +48 33 143526         |
>+--------------------------------------------------------------+

 

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD  

http://govt.us.oracle.com/ -- downloadable utilities  



Opinions are mine and do not necessarily reflect those of Oracle Corporation  

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Sat Jan 10 1998 - 00:00:00 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US