Re: getting rownum 500000 - 500050 from a database

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.

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
> > |
> > |
> >
> >
Received on Tue Nov 16 1999 - 05:43:37 CST