Re: How can I selec rows in physical order?

In article <3952223f.9830485_at_super.news-ituk.to>,   See Message body for real address wrote:
>
> The 'physical order' ( if by that you mean the actual location of each
 record
> on the storage device) of data in an Oracle database is
 undeterminable....
>

Not quite true. Using the DBMS_ROWID package in Oracle 8, or simply the ROWID value in Oracle 7 you can determine what address, in a given datafile, each row has. Since ROWIDs are generated sequentially returning the rows in the order they were inserted into the table would be a fairly simple task:

In Oracle 7

select mycolumn
from mytable
order by rowid;

In Oracle 8 (presuming that the table is not partitioned or stored in multiple tablespaces):

select mycolumn
from mytable
order by dbms_rowid.rowid_to_restricted(rowid, 0);

> Rownum is a counter that limits the # of rows returned...
>

ROWNUM is a pseudo-column that CAN be used to limit the number of rows returned from a query:

select mycolumn
from mytable
where rownum < 51;

will return the first 50 rows of the result set. In and of itself it does not limit the number of rows returned.

ROWNUM can also be used to return a specific row in a result set based upon return order:

select mycolumn
from mytable
where rownum = 60;

will return the 60th row in the result set. This may or may not be the same row over a period of time but it will always be the 60th record returned by the query.

> sergey_s_at_my-deja.com wrote:
>
> >Does ROWNUM guarantee physical order of the rows in a table?
> >If not, how can I get rows out in their physical order?
> >
> >Thanks!
> >
> >Sergey
> >
> >
> >Sent via Deja.com http://www.deja.com/
> >Before you buy.
>
> -----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
> http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
> -----== Over 80,000 Newsgroups - 16 Different Servers! =-----
>

--
David Fitzjarrell
Oracle Certified DBA


Sent via Deja.com http://www.deja.com/
Before you buy.