Home » SQL & PL/SQL » SQL & PL/SQL » Order by clause difference (Oracle 11.1.0.6.0)
| Order by clause difference [message #355465] |
Fri, 24 October 2008 04:47  |
ganeshsv
Messages: 51
Registered: January 2006
|
Member |
|
|
HI All,
I am migrating from Oracle 9.2.0.6.0 to Oracle 11.1.0.6.0 version. When I execute the below query, data is displayed in different order.
CREATE TABLE TAB_A (col VARCHAR2(20), col2 VARCHAR2(20));
CREATE TABLE TAB_B (col VARCHAR2(20), col2 VARCHAR2(20));
INSERT INTO TAB_B VALUES ('b', 'v');
INSERT INTO TAB_A VALUES ('a','v');
INSERT INTO TAB_A VALUES ('a','b');
INSERT INTO TAB_A VALUES ('a','m');
INSERT INTO TAB_A VALUES ('a','a');
INSERT INTO TAB_A VALUES ('a','q');
Commit;
Below is the query
SELECT a.col, a.col2 FROM TRY_A a
LEFT OUTER JOIN TAB_B b
ON (a.col=b.col)
ORDER BY a.col, a.col2
In oracle 11g it gives following output
COL COL2
-------------------
a v
a b
a m
b a
b q
And in ORacle 9i, am getting
Col Col2
-------------------
a a
a b
a m
a q
a v
Can you please let me know is this driven by any parameters?
hanks in advance.
Regards,
GS
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Order by clause difference [message #355477 is a reply to message #355473] |
Fri, 24 October 2008 06:02  |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
I suspect that the problem is not unrelated to the script creating tables TAB_A and TAB_B, and the query using table TRY_A.
If you change the Sql to use the tables in the test case, you get this:CREATE TABLE TAB_A (col VARCHAR2(20)
,col2 VARCHAR2(20));
CREATE TABLE TAB_B (col VARCHAR2(20), col2 VARCHAR2(20));
INSERT INTO TAB_B VALUES ('b', 'v');
INSERT INTO TAB_A VALUES ('a','v');
INSERT INTO TAB_A VALUES ('a','b');
INSERT INTO TAB_A VALUES ('a','m');
INSERT INTO TAB_A VALUES ('a','a');
INSERT INTO TAB_A VALUES ('a','q');
Commit;
SELECT a.col, a.col2
FROM TAB_A a
LEFT OUTER JOIN TAB_B b ON (a.col=b.col)
ORDER BY a.col, a.col2
COL COL2
-------------------- --------------------
a a
a b
a m
a q
a v
Amazingly, it looks fine.
|
|
|
|
| Re: Order by clause difference [message #355559 is a reply to message #355477] |
Sat, 25 October 2008 05:15 |
ganeshsv
Messages: 51
Registered: January 2006
|
Member |
|
|
Hi All,
There was some problem in my previous data. Please check the below data.
DROP table TAB_A ;
DROP table TAB_B;
CREATE TABLE TAB_A (COL_A VARCHAR2(10), COL_B VARCHAR2(20));
CREATE TABLE TAB_B (COL_A VARCHAR2(10), COL_B VARCHAR2(20));
INSERT INTO TAB_B VALUES ('B', 'V');
INSERT INTO TAB_A VALUES ('A','V');
INSERT INTO TAB_A VALUES ('A','B');
INSERT INTO TAB_A VALUES ('A','M');
INSERT INTO TAB_A VALUES ('A','A');
INSERT INTO TAB_A VALUES ('A','Q');
COMMIT;
SELECT
A.COL_A || ' '|| NVL(B.COL_B,'0') || ' ' ||A.COL_B DATA
FROM TAB_A A
LEFT OUTER JOIN TAB_B B ON (A.COL_A=B.COL_A)
ORDER BY A.COL_A, NVL(B.COL_B,'0'), A.COL_A;
In Oracle 9i and 10g, data is displayed as
DATA
------
A 0 Q
A 0 A
A 0 M
A 0 B
A 0 V
And in Oracle 11g version,
DATA
------
A 0 Q
A 0 A
A 0 V
A 0 B
A 0 M
Can you please let me know the reason for this order difference?
Thanks in advance,
GS
|
|
|
|
| Re: Order by clause difference [message #355563 is a reply to message #355559] |
Sat, 25 October 2008 06:39 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
Your ORDER BY reads thus:
ORDER BY A.COL_A, NVL(B.COL_B,'0'), A.COL_A;
It should be:
ORDER BY A.COL_A, NVL(B.COL_B,'0'), A.COL_B;
Since every row has the same value for A.COL_A and NVL(B.COL_B,'0'), they come out in essentially a random order. ie. Differently on different databases.
Ross Leishman
|
|
|
|
| Re: Order by clause difference [message #355569 is a reply to message #355563] |
Sat, 25 October 2008 07:41 |
ganeshsv
Messages: 51
Registered: January 2006
|
Member |
|
|
HI Ross Leishman,
Thanks. But this is my existing code. And the ORDER by clause is
"ORDER BY A.COL_A, NVL(B.COL_B,'0'), A.COL_A;" only.
As you mentioned, it could be random order. However always this random logic produces result in same order.
But the results are matching in Oracle 9i,10g and 11g if the queries is as below (i.e without last column in ORDER by clause)
SELECT
A.COL_A || ' '|| NVL(B.COL_B,'0') || ' ' ||A.COL_B DATA
FROM TAB_A A
LEFT OUTER JOIN TAB_B B ON (A.COL_A=B.COL_A)
ORDER BY A.COL_A, NVL(B.COL_B,'0');
So is there any specific parameter that makes the difference?
Thanks in advance.
Regards,
GS
|
|
|
|
| Re: Order by clause difference [message #355571 is a reply to message #355563] |
Sat, 25 October 2008 08:18 |
ganeshsv
Messages: 51
Registered: January 2006
|
Member |
|
|
Hi All,
Thanks a lot for your response. It seems hidden parameter "_newsort_enabled" was set to true in my ORACLE 11g and hence the sorting result was different.
When I reset it to false, my results match with ORACLE 9i and 10g result.
Thanks.
Regards,
GS
|
|
|
|
|
|
| Re: Order by clause difference [message #355598 is a reply to message #355569] |
Sun, 26 October 2008 01:25 |
Frank
Messages: 7901
Registered: March 2000
|
Senior Member |
|
|
| ganeshsv wrote on Sat, 25 October 2008 14:41 |
As you mentioned, it could be random order. However always this random logic produces result in same order.
|
Well, apparently it does NOT.
You saw one example of how the always-the-same-random order could change. There are numerous other ways it can happen.
Do not expect to get rows in a particular order, if you did not specify the order by to do so.
|
|
|
|
Goto Forum:
Current Time: Sat Feb 15 21:02:37 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
