Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> 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.Received on Thu Jun 22 2000 - 00:00:00 CDT