Re: getting rownum 500000 - 500050 from a database

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
> |
> |
>
>
Received on Mon Nov 15 1999 - 23:56:20 CST