Re: odd ( to me ) question

In message <1176339675.188623.205470_at_n59g2000hsh.googlegroups.com>, Ben <balvey_at_comcast.net> writes
>On Apr 11, 6:15 pm, "joel garry" <joel-ga..._at_home.com> wrote:
>> On Apr 11, 1:24 pm, "Ben" <bal..._at_comcast.net> wrote:
>>
>> > 9.2.0.5 EntEd AIX5L
>>
>> > If I have a read-only table and issue a select statment with no order
>> > by clause, will the data be returned in the same order every time?
>>
>> http://jonathanlewis.wordpress.com/2007/03/05/ordering/
>>
>> jg
>> --
>> @home.com is bogus.http://volokh.com/posts/chain_1176127892.shtml
>
>
>
>
>
>Sorry, I guess I was kind of beating about the bush, I really was just
>wondering how oracle sorts by default with no order by clause when
>selecting from one table. I thought maybe rowid or primary key but I
>tried both it seemed like neither was the answer. I'm not trying to
>rely on not using an order by, but the question was asked of me by a
>user and I just didn't really know. I probably should but the thought
>never really crossed my mind because of the order by.
>

You can't rely on not using an order by.

There is no default sort order - the order of a resultset largely depends on the access path used.

If you do "select * from table" oracle will do a full table scan and the rows will be returned in the order the exist on disk which MAY be the order of insertion (unless there have been deletions and updates). If you use a where clause, oracle may use an index and the order will be the order of the index columns. If you join to another table all bets are off.

There are some very simple cases where you can almost predict the order, but almost isn't good enough. And why bother? The SQL specification says that you can't rely on the order unless you use an order by clause - so why try to otherwise?

-- 
Jim Smith
Ponder Stibbons Limited <http://oracleandting.blogspot.com/>
RSS <http://oracleandting.blogspot.com/atom.xml>