Re: odd ( to me ) question

On Apr 12, 7:13 am, "Ben" <bal..._at_comcast.net> wrote:
> 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.- Hide quoted text -
>
> - Show quoted text -

In layman's terms:

"By design, there is *no* default sort sequence. Without an ORDER BY clause, the ordering of the returned rows is undefined and unpredictable." Received on Thu Apr 12 2007 - 08:02:01 CDT