Solved

Issue in default Sorting in Oracle 10g

Posted on 2009-04-13
4
761 Views
Last Modified: 2012-05-06
Hi,
  My application runs in eight independent instance of Oracle server. only user form level data will be same in all the instances, data entered by user in one instance will not be available in other instance. I really have strange issue. I am performing a select query withour order by clause on the user form level data, out of 8 instance 7 instance are returning that common data in same order, but one instance is returning in different. I am seeing this difference only after migrating the backend from Oracle 9i to Oracle 10g.

 Requesting to help me to understand why such a behaviour, and let me know how to resolve the problem.
0
Comment
Question by:v_c_krishnan
4 Comments
 
LVL 14

Expert Comment

by:ajexpert
ID: 24132326
Can you post or compare the explain plan of the two instances for the same queries?
0
 
LVL 40

Accepted Solution

by:
mrjoltcola earned 250 total points
ID: 24132368
You cannot rely on "default" ordering if you don't specify an ORDER BY, Oracle will order by different ways, depending on what type of table it is, and in what order the data was inserted.

Proper fix: Resolve the problem by adding explicit ORDER BY

There are other ways that you can affect the ordering with indexes or clusters, but these are low-level physical things that you should not depend on for proper set ordering, because they could change, as you have seen.
0
 
LVL 15

Assisted Solution

by:Franck Pachot
Franck Pachot earned 250 total points
ID: 24151615
Hi,
To say it in another way, SQL is a declarative language: you say what result you want, you don't say how it is retreived. So if you dont say that you want an ordered result (and the only way is ORDER BY) then the order of the result will depend on the way oracle gets the data.
If you need ordered result and don't have an ORDER BY, then you have an important bug in your application. It showed up here because of different oracle version, but the issue can happen anytime in any instance (when some parameters changes, when workload changes, when data changes, etc.)
Regards,
Franck.
0

Featured Post

Secure Your Active Directory - April 20, 2017

Active Directory plays a critical role in your company’s IT infrastructure and keeping it secure in today’s hacker-infested world is a must.
Microsoft published 300+ pages of guidance, but who has the time, money, and resources to implement? Register now to find an easier way.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

679 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question