Re: default select order
From: Mark D Powell <Mark.Powell_at_eds.com>
Date: Thu, 8 Jan 2009 08:29:35 -0800 (PST)
Message-ID: <82515b80-0235-4c0c-8a44-0ce4becbae7a_at_d32g2000yqe.googlegroups.com>
On Jan 7, 2:29 pm, ddf <orat..._at_msn.com> wrote:
> On Jan 7, 11:46 am, Mark D Powell <Mark.Pow..._at_eds.com> wrote:
>
>
>
>
>
> > 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 --- Hide quoted text -
>
> > - Show quoted text -
>
> Both sets of data are being selected from the same table in the 10g
> version:
>
> SELECT X FROM TABLE_Z WHERE SOMETHING ='SOMETHING' ;
> SELECT Y FROM TABLE_Z WHERE SOMETHING='SOMETHING' ;
>
> Notice the WHERE clause is the same in both queries, only the column
> selected varies. The OP states, and illustrates, that depending upon
> the column in the SELECT list the order of the results varies from the
> same table in the 10gR2 release:
>
> "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."
>
> As far as I know such an order variance shouldn't occur, and I cannot
> produce the OP's posted result with 10.2.0.3.0. This is not a
> question of behaviour between two releases of Oracle, it's differing
> behaviour for the same table and data in release 10gR2 (which patch
> level is still unknown). Given that, the query plans should be the
> same for the two results, leaving one to wonder if this is a bug in
> 10.2.0.1.0 fixed in subsequent patchsets.
>
> David Fitzjarrell- Hide quoted text -
>
> - Show quoted text -
Date: Thu, 8 Jan 2009 08:29:35 -0800 (PST)
Message-ID: <82515b80-0235-4c0c-8a44-0ce4becbae7a_at_d32g2000yqe.googlegroups.com>
On Jan 7, 2:29 pm, ddf <orat..._at_msn.com> wrote:
> On Jan 7, 11:46 am, Mark D Powell <Mark.Pow..._at_eds.com> wrote:
>
>
>
>
>
> > 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 --- Hide quoted text -
>
> > - Show quoted text -
>
> Both sets of data are being selected from the same table in the 10g
> version:
>
> SELECT X FROM TABLE_Z WHERE SOMETHING ='SOMETHING' ;
> SELECT Y FROM TABLE_Z WHERE SOMETHING='SOMETHING' ;
>
> Notice the WHERE clause is the same in both queries, only the column
> selected varies. The OP states, and illustrates, that depending upon
> the column in the SELECT list the order of the results varies from the
> same table in the 10gR2 release:
>
> "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."
>
> As far as I know such an order variance shouldn't occur, and I cannot
> produce the OP's posted result with 10.2.0.3.0. This is not a
> question of behaviour between two releases of Oracle, it's differing
> behaviour for the same table and data in release 10gR2 (which patch
> level is still unknown). Given that, the query plans should be the
> same for the two results, leaving one to wonder if this is a bug in
> 10.2.0.1.0 fixed in subsequent patchsets.
>
> David Fitzjarrell- Hide quoted text -
>
> - Show quoted text -
Now I understand what you were referring to. I have to agree though I wonder if some of the target buffers already being in the buffer cache are affecting the results. I have seen where the key selected from an IOT does not return in order. My guess which I never got to test is that it was due to blocks already being in the buffer cache and being accessed in cached rather than retrieved order.
Without the same full version being available, trying to duplicate the problem may be impossible.
- Mark D Powell --