Re: How can I selec rows in physical order?

<ddf_dba_at_my-deja.com> wrote in message
news:8itcm3$85j$1_at_nnrp1.deja.com...
> 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:

true, the ROWID pseudocolumn is a representation of the "physical
address" of the row in a datafile, but...

the "sequential" nature of the ROWIDs is an observable side-effect of
the behavior of Oracle, and should not be confused with a guarateed
behavior or an adherence to some sort of standard. There are several
cases, in fact, where ROWIDs are not in the same order that rows are
inserted into a table.

For example, what happens to ROWIDs when a table is reorganized ?
When a previously "full" block is popped back onto the freelist, and is
available for use by a subsequent insert ?

>
> 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.
>
Received on Sun Jun 25 2000 - 00:00:00 CDT