Improve company productivity with a Business Account.Sign Up

x
?
Solved

Issue in default Sorting in Oracle 10g

Posted on 2009-04-13
4
Medium Priority
?
771 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
3 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: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

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…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

585 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