Re: How can I selec rows in physical order?

In article <8it99g$gdh$1_at_soap.pipex.net>,   "Niall Litchfield" <niall.litchfield_at_doial.pipex.com> wrote:
> Prior to Oracle 8 you could - I believe do this using ROWID. After
 Oracle8
> the file reference is relative and I believe that you cannot do what
 you
> wish to.
>
> I would strongly question the motivation for doing this - In an RDBMS
 (any
> RDBMS) the concept of first record does not really exist. Why do you
 wish to
> do such an apparently absurd thing?
>
> --
> Niall Litchfield
> Oracle DBA
> Audit Commission UK
> <sergey_s_at_my-deja.com> wrote in message
 news:8ir4mg$kqa$1_at_nnrp1.deja.com...
> > 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.
>
>

Niall is right, and wrong, in his answer. Yes, the file number is relative to the tablespace and should a table span multiple tablespaces it would be difficult, if not impossible, to order the data by ROWID. If, however, the table is resident in a single tablespace the DBMS_ROWID package could be used to obtain the desired results:

select [column list]
from [table]
order by dbms_rowid.rowid_to_restricted(rowid,0);

This converts the extended Oracle 8 ROWID to a restricted Oracle 7 ROWID, allowing the query to order the data by file, block and row. Again, this is presuming that the table resides in only one tablespace; this will not likely work on partitioned tables as they span tablespaces.

--
David Fitzjarrell
Oracle Certified DBA


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