Re: internal order of oracle tables

In article <Cs6Kxz.9D8_at_gremlin.nrtc.northrop.com>

           msallwas_at_world.nad.northrop.com "Michael Sallwasser" writes:

> In article <Cs625A.84H_at_gremlin.nrtc.northrop.com> mdreis_at_world.nad.northrop.com
> (Martin Dreis) writes:
> >is there anyway to have oracle keep a table in order by
> >primary keys? just wondering if we can get away from having
> >to use order by on some of our selects.
>
> It has been my experience that if Oracle 6.0 performs a full-table scan it
> will return rows in the order in which they were inserted. (I do not know if
> this holds true if there are intervening deletes or if you insert multiple
> rows with a single SQL statement.)
>
> Oracle does not, and should not, guarantee that this will be the
> case. It is inconsistent with relational database concepts as I have
> understood them. If you *must* have the rows in sorted sequence, you
> must specify an ORDER BY clause.

Michael Sallwasser is right on the button. Relationally, there is no such thing as a 'right' order for the rows in a table. In fact, it helps if one remembers that the table as one visualises it does not actually exist. It's just a convenient concept for imaginary modelling. For instance, if you have a (very) wide table, the component parts of the row might not even be on a contiguous part of the disk.

ORDER BY is essential to retrieve ordered rows.

M

-- 
-----------------------------------------------------------------------------
                                                    MarkB_at_aboy.demon.co.uk
Lyric Quiz of the Week: "Even Presidents have newspaper lovers.
                         Ministers go crawling under covers."