Home » SQL & PL/SQL » SQL & PL/SQL » Different order in oracle 8i and 10g
Different order in oracle 8i and 10g [message #297744] Sun, 03 February 2008 04:52 Go to next message
divektolia
Messages: 3
Registered: February 2008
Junior Member
Hi All,

When I execute the same query in Oracle 8i and 10g i get different results .

create table test(id1 NUMBER(7),id2 NUMBER(7),id3 NUMBER(7));
insert into test values (1,2,3);
insert into test values (1,3,3);
insert into test values (2,3,3);
insert into test values (4,3,3);
insert into test values (3,3,3);
insert into test values (5,1,3);
commit;

Output for Oracle 8i

ORACLE_8i> select id1,id2 from test order by id3;

ID1 ID2
---------- ----------
1 2
1 3
2 3
4 3
3 3
5 1

6 rows selected.

Output for Oracle 10g

ORACLE_10g> select id1,id2 from test order by id3;

ID1 ID2
---------- ----------
1 2
1 3
5 1
4 3
3 3
2 3
6 rows selected.



Below are the NLS Session parameters for both Oracle 8i and 10g

ORACLE_8i> select * from nls_session_parameters;

PARAMETER VALUE
------------------------------ ----------------------------------------
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT YYYYMMDD
NLS_DATE_LANGUAGE AMERICAN
NLS_SORT BINARY
NLS_TIME_FORMAT HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZH:TZM
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZH:TZM
NLS_DUAL_CURRENCY $
NLS_COMP BINARY

ORACLE_10g> select * from nls_session_parameters;

PARAMETER VALUE
------------------------------ ----------------------------------------
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT YYYYMMDD
NLS_DATE_LANGUAGE AMERICAN
NLS_SORT BINARY
NLS_TIME_FORMAT HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY $
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE

Any help on this would be highly appreciated.

Regards,
Divek.



Re: Different order in oracle 8i and 10g [message #297745 is a reply to message #297744] Sun, 03 February 2008 05:02 Go to previous messageGo to next message
Littlefoot
Messages: 20900
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
As 'id3 = 3' in all records, what did you, actually, expect? Unless you specify the desired order, there's no guarantee that you'll get it (no matter what). Furthermore, your query is likely to fetch records in different order tomorrow or next week or next month (depending on what happened to the database - export/import might change row order, someone might delete records and insert them afterwards, etc.).

A conclusion: write the correct ORDER BY clause and you'll be SURE that all the records will be in the desired order.
Re: Different order in oracle 8i and 10g [message #297746 is a reply to message #297744] Sun, 03 February 2008 05:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64137
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And then?
Don't you have you result order by id3?
Yes, so you have the result you asked.
Why do you want different databases in different versions of Oracle returns you in the same order when you don't ask a full order (an order on a unique key)?

Regards
Michel
Re: Different order in oracle 8i and 10g [message #297769 is a reply to message #297745] Sun, 03 February 2008 11:06 Go to previous messageGo to next message
divektolia
Messages: 3
Registered: February 2008
Junior Member
Hi,

I really appreciate your quick responses to my query . Can you tell me what is the correct way to write an order by clause,so that i get the same order of rows fetched in different database versions. I mean is there any best practices to be followed to make sure that your Order By clause fetched data in the same order all the time in different database versions.

Thanks in advance.
Divek.
Re: Different order in oracle 8i and 10g [message #297771 is a reply to message #297769] Sun, 03 February 2008 11:09 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
If you want a guaranteed order, each and everytime you perform a query, you have to make sure that the combination of fields you order by has unique values.
Re: Different order in oracle 8i and 10g [message #297774 is a reply to message #297771] Sun, 03 February 2008 11:18 Go to previous messageGo to next message
Michel Cadot
Messages: 64137
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Of course, if you order on a string you have to know that this will depend on your language.

Regards
Michel
Re: Different order in oracle 8i and 10g [message #297777 is a reply to message #297774] Sun, 03 February 2008 11:31 Go to previous messageGo to next message
divektolia
Messages: 3
Registered: February 2008
Junior Member
ok, so I should make sure that columns in my order by clause always give a unique value (use combination of primary keys of tables from which i am fetching data). If i am not able to achieve this, then there is no guarantee that there will be a fixed order across database versions.

Is my understanding correct ?

Thanks a lot.
It was a great help.

Regards,
Divek.
Re: Different order in oracle 8i and 10g [message #297780 is a reply to message #297777] Sun, 03 February 2008 11:53 Go to previous messageGo to next message
Littlefoot
Messages: 20900
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
In other words, ORDER BY clause might/should look like
ORDER BY id3, id1, id2
If it isn't good enough, perhaps you should consider using ID column (populated by a SEQUENCE, for example) which will uniquely identify rows in a table.
Re: Different order in oracle 8i and 10g [message #297852 is a reply to message #297777] Mon, 04 February 2008 00:31 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
divektolia wrote on Sun, 03 February 2008 18:31
If i am not able to achieve this, then there is no guarantee that there will be a fixed order across database versions.

This behavior is not restricted to different database versions, but can also occur within a single instance without any change to the software. The optimizer can decide to use a different access-path, physical distribution of data may change (eg due to delete/insert/update actions), etcetera.
Previous Topic: How to generate a spool file without an extension name (.lst,.txt etc...)
Next Topic: What is the usage of For Update Clause and Where current of
Goto Forum:
  


Current Time: Thu Dec 08 06:19:35 CST 2016

Total time taken to generate the page: 0.09046 seconds