[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Issue in default Sorting in Oracle 10g

Posted on 2009-04-13
4
Medium Priority
?
767 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 1000 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 1000 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

650 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