?
Solved

Issue in default Sorting in Oracle 10g

Posted on 2009-04-13
4
Medium Priority
?
766 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

[Webinar] How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them.

Question has a verified solution.

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

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows how to recover a database from a user managed backup
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses

765 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