Home » SQL & PL/SQL » SQL & PL/SQL » Order of Data Dispaly / printing has changed! (Oracle10g, 10.2.0.1.0, RHL 5 SE)
Order of Data Dispaly / printing has changed! [message #407711] Thu, 11 June 2009 04:02 Go to next message
msmallya77
Messages: 28
Registered: June 2009
Location: Ahmedabad
Junior Member
This we have obsereved after exporting data from Oracle 9i(9.2.0.4.0) RHL 9 and importing into Oracle 10g (10.2.0.1.0) RHL 5 SE

Can any one tell me why it is happening and solution if any (other than to use order by clause).

Because VB application which was running properly connected to Oracle 9i, now it is displaying with different order while connecting to Oracle 10g. This is happening in Help (list of values), and in reports.

Also from Sql prompt display order changes. Following is the observation.

In oracle 10g
Quote:
select * from abc;

gives the output as
Quote:
c1 c2 c3 c4
-- -- -- --
01 02 01 A1
01 02 02 A2
01 02 03 A3
01 02 04 A4
01 02 05 A5
01 02 06 A6
01 02 07 A7

where as after adding exists clause, ie
Quote:

select * from abc a
where exists (select * from xyz b
where a.c1=b.c1 and
a.c2=b.c2 and
a.c3=b.c3)

gives the output as
Quote:
c1 c2 c3 c4
-- -- -- --
01 02 06 A6
01 02 01 A1
01 02 02 A2
01 02 03 A3
01 02 04 A4
01 02 05 A5
01 02 07 A7

Secon output is strange, though data returned is ok.
but the Order has changed! (first row)

Also I have checked xyz table
Quote:
select * from xyz where c1='01' and c2='02'

gives output as
Quote:
c1 c2 c3 c4
-- -- -- --
01 02 06 A6
01 02 01 A1
01 02 02 A2
01 02 03 A3
01 02 04 A4
01 02 05 A5
01 02 07 A7

This means it has returned result in the order of xyz table?

This type of behaviour also noticed in some of the reports printed and displaying List of Values

Any suggestions?

Thanking you in advance.

Murlidhar S Mallya
Re: Order of Data Dispaly / printing has changed! [message #407712 is a reply to message #407711] Thu, 11 June 2009 04:07 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
If you don't use a ORDER BY clause, then the order the data is return in is random.

The only solution is to use an order by clause.
Re: Order of Data Dispaly / printing has changed! [message #407713 is a reply to message #407712] Thu, 11 June 2009 04:15 Go to previous messageGo to next message
msmallya77
Messages: 28
Registered: June 2009
Location: Ahmedabad
Junior Member
Thanks for your immediate feedback.

But in Oracle 9i we have never observed this behaviour. And there was no issue. Now suddenly the issue has comeup only after changing from 9i to 10g (from application users complaining list of values order has changed as againt earlier used to display).

Regards,

M S Mallya
Re: Order of Data Dispaly / printing has changed! [message #407714 is a reply to message #407713] Thu, 11 June 2009 04:23 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
There are of course a lot of changes between the versions.

It might be that the optimizer does the query differently, or during the import the rows where loaded into different segments in the datafile, etc.....

Fact is, if you don't use an order by the data is returned in random order. The order might stay the same for years, but it can change at any time.

Just like you can't rely on the fact that you have "driven through that stop sign hundreds of times, and there NEVER was another car", one day there might be an 40-ton truck that hits you. Very Happy

Re: Order of Data Dispaly / printing has changed! [message #407715 is a reply to message #407713] Thu, 11 June 2009 04:25 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It does not matter how 9i worked, 10g does it differently.
You took profit of a 9i side effect (that appears only in specifics circonstances and NOT in all cases) that no more exists in 10g.

Regards
Michel
Re: Order of Data Dispaly / printing has changed! [message #407761 is a reply to message #407714] Thu, 11 June 2009 08:01 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
ThomasG wrote on Thu, 11 June 2009 05:23

Just like you can't rely on the fact that you have "driven through that stop sign hundreds of times, and there NEVER was another car", one day there might be an 40-ton truck that hits you. Very Happy



Excellent Thomas. Good real world analogy.
Re: Order of Data Dispaly / printing has changed! [message #407793 is a reply to message #407761] Thu, 11 June 2009 11:59 Go to previous message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Why, Thankyou. Thankyouverymuch. </Elvis>
Previous Topic: ORA-29283: invalid file operation
Next Topic: Procedure missing to execute
Goto Forum:
  


Current Time: Sat Dec 10 16:30:15 CST 2016

Total time taken to generate the page: 0.04991 seconds