Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Query returning results in different order

Re: Query returning results in different order

From: Thomas Kyte <tkyte_at_oracle.com>
Date: 20 Jul 2002 15:40:46 -0700
Message-ID: <ahcote013tk@drn.newsguy.com>


In article <3D39D166.4060708_at_snafu.de>, "Dr. says...
>
>Hi Jeff,
>
>this behavior results from different execution plans. Check on both
>systems with:
>

could just as simply result just from different order of insertion, or update, or deletion. do NOT rely on a query plan to give you the same data in the same order.

bottom line := if you have no order by, you have no right to expect the data to be presented in any particular order.

>SET AUTOTRACE ON
>stmt;
>SET AUTOTRACE OFF
>
>If role PLUSTRACE (needed for autotrace) is not available to you ask
>your DBA for it.
>
>Regards,
>Steffen
>
>Howard J. Rogers wrote:
>
>> "Jeff Turner" <zigjst_at_hotmail.com> wrote in message
>> news:c73aa58a.0207190539.7b3ad12d_at_posting.google.com...
>>
>>>I'm having a heck of a time figuring this out and I'm hoping someone
>>>can help.
>>>
>>>I've got a SQL statement that I can run on two different Oracle
>>>servers with different results. The data returned is the same, however
>>>the order of the result set is different.
>>>
>>>It returns correctly on an Oracle 8.1.6.0 server, but does not on
>>>8.1.6.3.0. The database schemas & data are identical on both servers.
>>>Here is the SQL statement:
>>>
>>>
>>>SELECT DISTINCT
>>>VTREATMENTS.TREATMENTID AS "Treatment ID",
>>>VAPPLICATIONS.APPLICATIONNUM AS "Application No",
>>>VSTUDYACTIVEINGREDIENTS.ISOFINTEREST AS "AI Of Interest"
>>>FROM
>>>VTREATMENTS, VAPPLICATIONS, VSTUDYACTIVEINGREDIENTS
>>>WHERE
>>>VSTUDYACTIVEINGREDIENTS.ISOFINTEREST= 'Yes' AND
>>>VTREATMENTS.TrialNo = VAPPLICATIONS.TrialNo AND
>>>VTREATMENTS.TrialNo = VSTUDYACTIVEINGREDIENTS.TrialNo AND
>>>VTREATMENTS.TreatmentNo = VAPPLICATIONS.TreatmentNo AND
>>>VAPPLICATIONS.TrialNo = VSTUDYACTIVEINGREDIENTS.TrialNo AND
>>>VTREATMENTS.TrialNo = 284 AND VSTUDYACTIVEINGREDIENTS.StudyNo = 121
>>>
>>>
>>
>>
>> And the 'order by' clause would be ..., er...., where, exactly?
>>
>> Unless there's an order by, rows get returned in 'windspeed factor' order.
>>
>> On alternate Thursdays.
>>
>> Regards
>> HJR
>>
>>
>>
>>
>>
>>
>>
>>>The statement is generated by an application and I have no control
>>>over it (iow I can't just tack on an ORDER BY to it), but I can
>>>control the order of the views it is selecting from. I also have no
>>>control over changing the version of the Oracle server.
>>>
>>>The funny thing about this is if I remove the first WHERE clause
>>>(ISOFINTEREST = 'Yes') it returns in the proper order on the 8.1.6.3
>>>server!
>>>
>>>any insight would be very appreciated.... thanks
>>>
>>
>>
>

--
Thomas Kyte (tkyte@oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp 
Received on Sat Jul 20 2002 - 17:40:46 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US