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 Go to next message
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 #355466 is a reply to message #355465] Fri, 24 October 2008 04:55 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
That's not a different order. That's different data.
Re: Order by clause difference [message #355467 is a reply to message #355465] Fri, 24 October 2008 04:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.

Use SQL*Plus and copy and paste your session.

Unless you encountered a (serious) bug (or oversimplified your case or built/created some locale specifities), it is not possible.

Regards
Michel

Re: Order by clause difference [message #355469 is a reply to message #355466] Fri, 24 October 2008 05:07 Go to previous messageGo to next message
ganeshsv
Messages: 51
Registered: January 2006
Member
HI,

Thanks. If you see my output data, its not different data. Its same data only but in different order .

Regards,
GS
Re: Order by clause difference [message #355473 is a reply to message #355469] Fri, 24 October 2008 05:19 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Explain the b's in the first column of the 11g output then..
Re: Order by clause difference [message #355477 is a reply to message #355473] Fri, 24 October 2008 06:02 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
rleishman
Messages: 3724
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #355573 is a reply to message #355571] Sat, 25 October 2008 10:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I'm glad you are happy with this result but in any case you can rely on it.

Regards
Michel

[Updated on: Sat, 25 October 2008 10:50]

Report message to a moderator

Re: Order by clause difference [message #355598 is a reply to message #355569] Sun, 26 October 2008 01:25 Go to previous message
Frank
Messages: 7880
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.
Previous Topic: What update index does?
Next Topic: Receiving parameters to Sql script through concurrent program
Goto Forum:
  


Current Time: Thu Dec 08 20:09:53 CST 2016

Total time taken to generate the page: 0.12005 seconds