Re: default select order

From: ddf <oratune_at_msn.com>
Date: Wed, 7 Jan 2009 07:39:11 -0800 (PST)
Message-ID: <249a4966-674a-48b0-a743-4a7bb4b34da9_at_s9g2000prm.googlegroups.com>

Comments embedded.

On Jan 7, 7: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.

Without an Order By clause then there is no issue as 'default' ordering doesn't exist.

> Now I'm wondering why 10g2 works like this (every time I query)? What
> determines the order of the returned rows?

Which release of 10gR2 are you using? 10.2.0.3 doesn't work as you report:

SQL> select *
  2 from v$version;

BANNER

---

Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi PL/SQL Release 10.2.0.3.0 - Production
CORE 10.2.0.3.0 Production
TNS for IBM/AIX RISC System/6000: Version 10.2.0.3.0 - Productio NLSRTL Version 10.2.0.3.0 - Production

SQL>
SQL> create table table_z(

  2          x varchar2(1),
  3          y varchar2(45),
  4          something varchar2(20)

  5 );

Table created.

SQL>
SQL> insert all
  2 into table_z
  3 values('A','Avoin', 'SOMETHING')
  4 into table_z
  5 values('V','Valmis', 'SOMETHING')
  6 into table_z
  7 values('E','Ei kaytossa', 'SOMETHING')   8 select * From dual;

3 rows created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> SELECT X FROM TABLE_Z WHERE SOMETHING ='SOMETHING' ; X
-
A
V
E

SQL>
SQL> SELECT Y FROM TABLE_Z WHERE SOMETHING='SOMETHING' ; Y

---

Avoin
Valmis
Ei kaytossa

SQL>
SQL> truncate table table_z;

Table truncated.

SQL>
SQL> insert all
  2 into table_z
  3 values('A','Avoin', 'SOMETHING')
  4 into table_z
  5 values('E','Ei kaytossa', 'SOMETHING')   6 into table_z
  7 values('V','Valmis', 'SOMETHING')
  8 select * From dual;

3 rows created.

SQL>
SQL> SELECT X FROM TABLE_Z WHERE SOMETHING ='SOMETHING' ; X
-
A
E
V

SQL>
SQL> SELECT Y FROM TABLE_Z WHERE SOMETHING='SOMETHING' ; Y

---

Avoin
Ei kaytossa
Valmis

SQL> Of course I'm working with a limited set of data.

> 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.

Again, which release (please report all four or five numbers, not '10r2') are you using?

> Column Y is not in primary key or any index. Is there a way
> to fix this  in the database ?
>
> Regards,
>
> Maija-Leena

David Fitzjarrell Received on Wed Jan 07 2009 - 09:39:11 CST