Re: internal order of oracle tables

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.

If you loaded the rows in sorted sequence, Oracle's sort performance may improve when you specified an ORDER BY clause. I have not tested this.

It is conceivable that Oracle could allow for sorted storage (and may do so under version 7.0) and could take advantage of that when performing the ORDER BY (essentially Oracle would know the records were in sequence and would not spend time sorting them.)

-- 
============================================================================
Michael Sallwasser  | Down one path is utter dispair and hopelessness. Down 
Northrop Grumman    | the other is total destruction. Let us choose wisely.