Hi , 

in oracle 10 g , the below query results in alphabetical order , there is no order by caluse. but when i am running the same query in oracle 11g , it gives me in random order. what is the problem here?

select distinct  Report_137__1_ . COUNTRY_NM   COUNTRY_NM  from (select distinct  v . CTMS_TRIAL_ALIAS_CODE ,  v . CLINICAL_TRIAL_ID ,  c . GEO_BOUNDARY_ID ,  c . COUNTRY_NM ,  c . CANCEL_INDC ,  c . STOP_INDC  from  CTMS_CLINICAL_TRIAL_ID_V   v ,  TRIAL_COUNTRY_V   c  where  v . CLINICAL_TRIAL_ID = c . CLINICAL_TRIAL_ID )  Report_137__1_

 

above is a  simple select statement. Thanks much.

Views: 582

Reply to This

Replies to This Discussion

The problem is simple: The query does not have an ORDER BY clause.

If you want Oracle to guarantee a specific order of a result set *every time you run a query* you have to use an ORDER BY.

But does this means in 10g we do not need to mention order by clause and still we get the result in alphabetical order i.e. ascending order.i understand that we need to mention order by clause to get the result in asc/desc order.

but why here in 10g i am getting the result in asc order without mentioning the order by clause and in 11g m not.

does 10g ,11g have diff in sorting the result set?

 

This means that the query's optimizer plan in 11g is not the same as 10g.

I suggest you read this blog post by Tom Kyte: http://tkyte.blogspot.com/2005/08/order-in-court.html

Thanks a lot , this does help. hope i would be able to make business user understand this ;) 

RSS

Oracle Community On

I'm not a fan of advertising, and so I will not be including any advertisements on OracleCommunity.net. However, managing this community does not come without cost! If you are willing to donate to help pay for the monthly community fees and domain services I accept Bitcoin and PayPal donations.

Donate Bitcoins

Badge

Loading…

Blog Posts

Android video recovery software

The entire construtors asserted come across android video recovery software french 5,000 words is made for both relatively easy in addition to excellent individuals, And also for next to anybody what person requires an google android os touch screen phone. Owners and similarly added if the 65 progressive speech treatments will be contributed to the application customer base soon enough but also 1000 keyword sentences will be added monthly to the. Major flaws that novice trisports people…

Continue

Posted by Donalds Snoi on January 15, 2017 at 6:14pm

Data Enrichment can Help You Derive Value from Your Data

The standardization, correction, and validation of existing data to maximize its efficiency is called data enrichment. As business expands and grows, it needs more accurate and refined data. Data, one of the most valuable assets of an organization,…

Continue

Posted by Alvaro Dee on January 11, 2017 at 9:21pm

How to Set Yourself Up For Success on eBay

One of the most popular marketplaces, eBay is setting new examples for competitors in terms of customer satisfaction. Its excellent services and quality of products has made it that popular. eBay…

Continue

Posted by Alvaro Dee on January 8, 2017 at 9:33pm

Know How to Save More Time to Focus and Grow Your Primary Business

Data is the most valuable asset of an organization. To get the most out of data, one needs to properly manage and maintain it. Accurate and easily-accessible business data can help in making informed decisions, enable better understanding, and improve…

Continue

Posted by Alvaro Dee on December 22, 2016 at 8:42pm

© 2017   Created by Steve Karam.   Powered by

Badges  |  Report an Issue  |  Terms of Service