Re: default select order

From: Mark D Powell <Mark.Powell_at_eds.com>
Date: Wed, 7 Jan 2009 09:46:37 -0800 (PST)
Message-ID: <4b725735-936f-4b2b-976c-41834768e354_at_t11g2000yqg.googlegroups.com>



On Jan 7, 10:52 am, ddf <orat..._at_msn.com> wrote:
> 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- Hide quoted text -
>
> - Show quoted text -

David, I do not understand your statement that the plans shoud be the same just because the SQL statements are the same since there are two different versions of Oracle involved and the CBO had numerous changes/ fixes applied to it between the mentioned releases. A lack of statistics or difference in statistics could result in different plans for the same SQL statement even if the plans really should be the same for optimal performance.

Depending on how the table data was migrated from one release to the newer one the table data may not even be in the same order which in itself could explain the result since apparently multiple rows are being returned. Characterset differences could also change the output order since this could change the indexing order of the data.

Your other post seems more aligned to what I would expect.

IMHO -- Mark D Powell -- Received on Wed Jan 07 2009 - 11:46:37 CST

Original text of this message