Re: default select order

From: ddf <oratune_at_msn.com>
Date: Wed, 7 Jan 2009 07:52:39 -0800 (PST)
Message-ID: <4b172102-4032-4a96-bc24-0a32da823803_at_k36g2000pri.googlegroups.com>

On Jan 7, 9:38 am, hpuxrac <johnbhur..._at_sbcglobal.net> wrote:
> 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 ).- Hide quoted text -
>
> - Show quoted text -

The plans should be the same since the table is the same in both queries and the WHERE clause is identical between them, and they are:

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

Execution Plan

---

Plan hash value: 403654761

---

| Id  | Operation                   | Name        | Rows  | Bytes |
Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |     8 |    88
|     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TABLE_Z     |     8 |    88
|     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | TABLE_Z_IND |     8 |
|     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---

   2 - access("SOMETHING"='SOMETHING')

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

---

Avoin
Valmis
Ei kaytossa

Execution Plan

---

Plan hash value: 403654761

---

| Id  | Operation                   | Name        | Rows  | Bytes |
Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |     8 |   128
|     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TABLE_Z     |     8 |   128
|     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | TABLE_Z_IND |     8 |
|     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---

   2 - access("SOMETHING"='SOMETHING')

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