Re: default select order

From: hpuxrac <johnbhurley_at_sbcglobal.net>
Date: Wed, 7 Jan 2009 07:38:16 -0800 (PST)
Message-ID: <49356933-dc4a-4d44-9284-bc02c77e4e09_at_o40g2000prn.googlegroups.com>

On Jan 7, 8:14 am, "Maija-Leena" <kangasm..._at_netti.fi> wrote:
> 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

If your applications are dependent on the order of rows that are returned they should be coded with an ORDER BY clause ... there's no guarantee from oracle that it won't change order otherwise.

Often changes in the order that rows are returned by ( without an ORDER BY clause ) are introduced when the optimizer selects a different retrieval strategy ( different plan ). Received on Wed Jan 07 2009 - 09:38:16 CST