Re: default select order
Date: Thu, 8 Jan 2009 13:57:38 -0800 (PST)
Message-ID: <1a5ad502-d5ca-4ade-b011-bdb5afa73378_at_l33g2000pri.googlegroups.com>
On Jan 8, 1:04 am, "Maija-Leena" <kangasm..._at_netti.fi> wrote:
> Hi,
>
> database version is 10.2.0.1.0.
>
> I'm going to fix this of course by adding the missing order by -clauses to
> the program, the problem is that I cannot deliver it very easily to the
> customer and that's why I was hoping to find some kind of work around that
> could be done in the database and would work even a little better for a week
> or two. I find this very interesting behaviour because it's different what
> I've used to in Oracle7.
>
> Thanks for all the answers,
>
> Maija-Leena
>
> "Maija-Leena" <kangasm..._at_netti.fi> wrote in message
>
> news:NU19l.116$Vl4.60_at_read4.inet.fi...
>
>
>
> > Hi,
> > we have changed from Oracle 7 to Oracle10g2 and found a mistake from our
> > program that didn't appear in the old environment.
>
> > That is, we have two select-statements that should return rows in the same
> > order but there is no order by -clause.
> > Now I'm wondering why 10g2 works like this (every time I query)? What
> > determines the order of the returned rows?
> > SELECT X FROM TABLE_Z WHERE SOMETHING ='SOMETHING' ;
>
> > A
>
> > V
>
> > E
>
> > SELECT Y FROM TABLE_Z WHERE SOMETHING='SOMETHING' ;
>
> > Avoin (refers to A)
>
> > Ei kaytossa (refers to E)
>
> > Valmis (refers to V)
>
> > If I change the first query to SELECT X,Y, then I get the same order than
> > the last query. Column Y is not in primary key or any index. Is there a
> way
> > to fix this in the database ?
>
> > Regards,
>
> > Maija-Leena
You've been lucky since Oracle 7. I believe it was in the first couple of pages of the application programming guide that it explicitly stated the order is never guaranteed.
There is and never has been no work around. Part of the reason is that without an order by, previous updates of the blocks within the SGA may or may not be committed for the read-consistent view of your selects. Check out the concepts manual about how Oracle creates a read-consistent view of the data - it may have to go all over the place, finding some data in memory, some having to get from disk, and some rebuilt from undo.
You may be able to demonstrate this with your own data by inserting/ committing some new rows from another session before and after your select begins. That's how I finally convinced some of my recalcitrant users, at least until they forgot and went back to their old, wrong, preconceived single-user type notions.
jg
-- _at_home.com is bogus. http://images.businessweek.com/ss/09/01/0107_scandals/?chan=rss_topSlideShows_ssi_5Received on Thu Jan 08 2009 - 15:57:38 CST