| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: getting rownum 500000 - 500050 from a database
A copy of this was sent to Marc Parkinson <marcpark_at_uswest.net>
(if that email address didn't require changing)
On Tue, 16 Nov 1999 04:43:37 -0700, you wrote:
>Thanks,
>
>But I am using 8.05
>Additionally, if you select row num > 1 you will not retrieve any rows.
>Also the order by is done after selection the rownums. I.E.
>The DB will select 50 rows and then order them. If will not
>Order the DB and then select the correct 50 rows.
>
In Oracle8i release 8.1 you can:
select *
from ( select a.*, rownum r
from ( select * from T order by c1, c2 ) a
where rownum <= 500050
)
It ain't going to be fast but'll get the 500,000 row AFTER ordering them all (trick is to push the order by into the subquery).
This only works in 8i and up.
>Thanks for your help.
>
>Marc Parkinson
>
>
>Tony BenBrahim wrote:
>
>> Not withstanding the previous answer
>>
>> select * from
>> (select ...,ROWNUM "R"
>> from ...)
>> where R>... and R<...
>> order by ...
>>
>> works in 8i (not on previous versions though) , but it is slow
>>
>> Tony
>>
>> Jerry Gitomer <jgitomer_at_erols.com> wrote in message
>> news:80qpns$ipl$1_at_autumn.news.rcn.net...
>> > Hi Marc,
>> >
>> > What you are trying to do won't work! Oracle may or may not
>> > store data at the end of a data file. In fact, if rows have been
>> > deleted from a table new rows may be stored in the space formerly
>> > occupied by the deleted rows. In addition, when doing full table
>> > scans, there is no guarantee that Oracle will start at the
>> > beginning of the file and read consecutive blocks. I seem to
>> > recall (and my memory may be faulty or the source I heard this
>> > from may be wrong, but....) if some blocks of a table are in
>> > memory and a full scan is required Oracle will start with the
>> > blocks that are already in memory. This means that the rownum of
>> > the data read from memory -- no matter where located in the
>> > table -- will have the lowest rownums.
>> >
>> > If you must retrieve data sequentially based on order of
>> > storage you will have to add a column to your table and then use
>> > a sequence to increment it as rows are added to the table.
>> >
>> > hth
>> > jerry gitomer
>> >
>> > Marc Parkinson wrote in message <3830B8E4.DB5D4B3B_at_uswest.net>...
>> > |I need to get row numbers 500000 through 500050 from a database.
>> > |I also need to do an order by clause before I retreive the data.
>> > |
>> > |Any ideas on how to do this other then reading the fir 500000
>> > rows of
>> > |data??
>> > |
>> > |
>> > |thanks
>> > |
>> > |Marc Parkinson
>> > |marcpark_at_uswest.net
>> > |
>> > |
>> >
>> >
--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'...
Current article is "Part I of V, Autonomous Transactions" updated June 21'st
Thomas Kyte tkyte_at_us.oracle.com Oracle Service Industries Reston, VA USA
Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Tue Nov 16 1999 - 07:23:51 CST
 |
 |