Home » SQL & PL/SQL » SQL & PL/SQL » 10G (Group by - Order By) doubt
10G (Group by - Order By) doubt [message #236217] Wed, 09 May 2007 06:21 Go to next message
atulrsingh
Messages: 55
Registered: November 2006
Location: Mumbai
Member
Hi Experts,

I have one doubt related to using group by clause in 10g

As in 9i, Does in 10gR2 Group by automatically initiates the Order by clause

Thnx
Atul



Re: 10G (Group by - Order By) doubt [message #236226 is a reply to message #236217] Wed, 09 May 2007 06:30 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
It does not and it NEVER did.
Re: 10G (Group by - Order By) doubt [message #236237 is a reply to message #236217] Wed, 09 May 2007 06:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Write in bold!

It does not and it NEVER did.

Regards
Michel
Re: 10G (Group by - Order By) doubt [message #236416 is a reply to message #236217] Wed, 09 May 2007 23:42 Go to previous messageGo to next message
atulrsingh
Messages: 55
Registered: November 2006
Location: Mumbai
Member
Yes it has always did... and now in 10g its not supporting these attribute of Group By..

Check out this out...

9i :

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
PL/SQL Release 9.2.0.8.0 - Production
CORE 9.2.0.8.0 Production
TNS for Solaris: Version 9.2.0.8.0 - Production
NLSRTL Version 9.2.0.8.0 - Production

SQL> select EMPID,EMPNAME from emp
2 group by empid,empname;

EMPID EMPNAME
---------- -------------------------------------------------------
101 JOHN
102 PRET
103 KATE
103 SMITH
104 JAMES
104 ramana rao
106 JOHN
108 MARTIN
911 raman

9 rows selected.


10g:

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Prod
PL/SQL Release 10.2.0.2.0 - Production
CORE 10.2.0.2.0 Production
TNS for Linux: Version 10.2.0.2.0 - Production
NLSRTL Version 10.2.0.2.0 - Production

SQL> select EMPID,EMPNAME from emp
2 group by empid,empname;

--- Only Group By

EMPID EMPNAME
---------- ------------------------------------------------------
108 MARTIN
102 PRET
103 SMITH
103 KATE
911 raman
101 JOHN
104 ramana rao
104 JAMES
106 JOHN

9 rows selected.

--- group by and order by

SQL> select EMPID,EMPNAME from emp
2 group by empid,empname
3 order by empid,empname;

EMPID EMPNAME
---------- ------------------------------------------------------
101 JOHN
102 PRET
103 KATE
103 SMITH
104 JAMES
104 ramana rao
106 JOHN
108 MARTIN
911 raman

9 rows selected.


So the conclusion is that in every query in the application group by has to be accompanied with order.

Pls Comment

Thnx

Re: 10G (Group by - Order By) doubt [message #236428 is a reply to message #236416] Wed, 09 May 2007 23:53 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

WRONG!

It seems to sort but it does not.

Regards
Michel

Re: 10G (Group by - Order By) doubt [message #236429 is a reply to message #236217] Wed, 09 May 2007 23:58 Go to previous messageGo to next message
atulrsingh
Messages: 55
Registered: November 2006
Location: Mumbai
Member
But my post has clearly shown that query has sorted in 9i and so there is certain BUG in 10g with the Group By Clause and so it doesn't sorts..

Re: 10G (Group by - Order By) doubt [message #236443 is a reply to message #236217] Thu, 10 May 2007 00:34 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Maybe you should study this article on AskTom, especially this and this post.

Can you find a documentation part stating your assumption? I found only this for 9i (for 10g is the same part here):
Quote:
Use the ORDER BY clause to order rows returned by the statement. Without an order_by_clause, no guarantee exists that the same query executed more than once will retrieve rows in the same order.

The fact the resultset is sorted under your certain conditions does not mean the resultset will be sorted EVERYTIME, even in 9i.

If you are still convinced this behaviour is a BUG and you have account on Oracle metalink, just file it as a BUG.
In that case, please, post its number here. Thanks.
Re: 10G (Group by - Order By) doubt [message #236446 is a reply to message #236429] Thu, 10 May 2007 00:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
@atulrsingh,

Scientifically speaking, an example does not prove anything but itself.
I can post many examples that PROVE that there may be no order and THIS is a proof of your error.

Regards
Michel
Re: 10G (Group by - Order By) doubt [message #236450 is a reply to message #236217] Thu, 10 May 2007 00:54 Go to previous messageGo to next message
atulrsingh
Messages: 55
Registered: November 2006
Location: Mumbai
Member
So the Conclusion is that...

In 9i, GROUP BY - ORDER BY was happening (for certain cases) but explicitly using ORDER BY is always suggested and supported conceptually.

But in 10G the oracle has ended this debate as ordering is not happening in any cases.

Thnx
Atul
Re: 10G (Group by - Order By) doubt [message #236458 is a reply to message #236450] Thu, 10 May 2007 01:06 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Those are different terms.

Imagine class of students. Now you tell girls to go left and boys to go right. You have grouped them. But, unless you tell them to stand in a line from the smallest to the tallest (i.e. order them by their height), there's no guarantee that they will do that. They might, of course, but you can't be sure.
Re: 10G (Group by - Order By) doubt [message #236463 is a reply to message #236450] Thu, 10 May 2007 01:11 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No.
Order may happen.
There is no order also means there may be an order.

Regards
Michel
Re: 10G (Group by - Order By) doubt [message #236467 is a reply to message #236217] Thu, 10 May 2007 01:14 Go to previous messageGo to next message
atulrsingh
Messages: 55
Registered: November 2006
Location: Mumbai
Member
So you mean sorting may happen some time but not always when order by not specified depending upon the data?
Re: 10G (Group by - Order By) doubt [message #236470 is a reply to message #236467] Thu, 10 May 2007 01:17 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Ok, assume we have 3 numbers: 1, 2, 3
Results may one of the following:
  1. 1,2,3
  2. 1,3,2
  3. 2,1,3
  4. 2,3,1
  5. 3,1,2
  6. 3,2,1

In case a) and f), result is ordered (asc or desc) but it is by accident.
So result may or may not be ordered but you can't know when.

Regards
Michel
Re: 10G (Group by - Order By) doubt [message #236522 is a reply to message #236470] Thu, 10 May 2007 02:58 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
When you ask Oracle to GROUP some data, you are just specifying the contruction of the groups - not the order in which they will be returned.

Prior to 10.2, Oracle used only ONE ALGORITHIM to perform a GROUP BY: it sorted the data (binary sort - no language fancy-pants stuff) and then grouped adjacent rows in the result set with the same values of the group-by variables.

In early versions (pre-9i I think, but don't quote me), the sort that performed was always ascending binary in the order of columns specified in the GROUP BY clause.

From 9i (or whatever) to 10.1, Oracle got smart and worked out that if the GROUP BY colums were a leading subset of the ORDER BY columns - or vice-versa - then it could sort the data just once (by the ORDER BY columns) and then group the results.

It could also do something similar without a sort if the GROUP BY columns were a leading subset of a concatenated index. The rows would be read via a full-scan or range-scan of the index and then the results grouped without a sort.

In this way, a GROUP BY would still return sorted results, but maybe the column-sequence of odering would not be what you expected.

In 10.2, Oracle introduced the Hash-Group-by algorithm, which is used in preference to the sort-group-by algorithm in almost all cases because it is faster. Using Hash-Group-By, the results are not sorted in any meaningful order. You may still see Sort-Group-By used in cases like those described above.

Ross Leishman
Re: 10G (Group by - Order By) doubt [message #236541 is a reply to message #236522] Thu, 10 May 2007 03:47 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
In early versions (pre-9i I think, but don't quote me), the sort that performed was always ascending binary in the order of columns specified in the GROUP BY clause.

This may lead to a conclusion that the result is always sorted in binary ascending order.
This is wrong.
Even in 8.0, you can have a different order.

Regards
Michel
Re: 10G (Group by - Order By) doubt [message #236909 is a reply to message #236217] Fri, 11 May 2007 05:13 Go to previous message
atulrsingh
Messages: 55
Registered: November 2006
Location: Mumbai
Member
Thnx everyone for the valuable inputs....

Previous Topic: Error with Pipelined Table Funtion in PL/SQL
Next Topic: Value modification of global variable
Goto Forum:
  


Current Time: Sun Dec 04 16:50:33 CST 2016

Total time taken to generate the page: 0.09494 seconds