Hi Jeff,
this behavior results from different execution plans. Check on both
systems with:
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
>>
>
>
Received on Sat Jul 20 2002 - 16:08:54 CDT
