Solved

Issue in default Sorting in Oracle 10g

Posted on 2009-04-13
4
759 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Need a replacement data type in Oracle 6 78
Oracle sql query 7 64
Component is listed with a Protocol more than once 3 32
Database Design Dilemma 6 39
Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
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 video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

831 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