Re: odd ( to me ) question

On Apr 12, 2:15 am, Jim Smith <usene..._at_ponder-stibbons.com> wrote:
> In message <1176339675.188623.205..._at_n59g2000hsh.googlegroups.com>, Ben
> <bal..._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>- Hide quoted text -
>
> - Show quoted text -

I'm not trying to rely on it, that's the point. I'm trying to explain to a user in laymans terms what order the data is coming out in. I don't like to tell a user that I don't know, I'll tell them that I'll have to find out. So I'm just trying to learn a little. I thought about it last night and I think in my case since there is a where clause it may be using an index order.
Like you were explaining though, I kind of assumed that the data would be returned in the order that it was written to disk or last update or something.

Once again, appreiciate all the good thoughts and knowledge. Received on Thu Apr 12 2007 - 07:13:50 CDT