10g and the Group By/Order By Clauses

[BJCooperIT]

In previous versions of Oracle it was never necessary to code an Order By clause if your Group By clause matched the sequence in which you wanted the data. This appears to have changed in 10g:

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 

SQL> create table bjc_test (fld1 varchar2(05), fld2 varchar2(05));

Table created

SQL> 
SQL> insert into bjc_test values ('DEF','001');

1 row inserted

SQL> insert into bjc_test values ('DEF','007');

1 row inserted

SQL> insert into bjc_test values ('DEF','005');

1 row inserted

SQL> insert into bjc_test values ('ABC','060');

1 row inserted

SQL> insert into bjc_test values ('ABC','005');

1 row inserted

SQL> insert into bjc_test values ('ABC','040');

1 row inserted

SQL> 
SQL> select FLD1, FLD2 from BJC_TEST
  2  group by FLD1, FLD2
  3  /

FLD1  FLD2
----- -----
[B]DEF   007
ABC   005
DEF   005
DEF   001
ABC   040
ABC   060[/B]

6 rows selected

SQL> 
SQL> select FLD1, FLD2 from BJC_TEST
  2  group by FLD1, FLD2
  3  order by FLD1, FLD2
  4  /

FLD1  FLD2
----- -----
ABC   005
ABC   040
ABC   060
DEF   001
DEF   005
DEF   007

6 rows selected

SQL>

My question is whether this a bug or a feature? If a feature, why? It appears that developers need to review all existing queries when upgrading to 10g.

^{Beware of false knowledge; it is more dangerous than ignorance. ~George Bernard Shaw}
Consultant Developer/Analyst Oracle, Forms, Reports & PL/SQL (Windows)
My website: Emu Products Plus

 

[SantaMufasa]

Barb,

In the Oracle classes that I taught, we always gave the disclaimer to the students that "although a GROUP BY statement appears to sort the data to effect the groupings, don't rely on that behaviour because you never know when Oracle might change their GROUP BY algorithm....Always use the ORDER BY clause to ensure that you receive the rows in the order you want them."

So, finally, with your help, I've proved my point to those many students that wondered, "Why code the extra line."

As far as "Bug vs. Feature", I'm certain that Oracle would call it a feature.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[dbtoo2001]

Oracle 9 propaganda:

"The GROUP BY clause groups rows but does not guarantee the order of the result set. To order the groupings, use the ORDER BY clause."

http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/statements_103a.htm#2066158

So, I'd be thinking this is not a bug, it's more of a feature.

 

[BJCooperIT]

In 8i, as I recall, the explain plans carried more overhead by adding the "Order By". The clause seemed unnecessary since the data came in the desired order and faster without it. Dave, I wish I had been in your class!

My apologies to any of my clients who might be upgrading.

^{Beware of false knowledge; it is more dangerous than ignorance. ~George Bernard Shaw}
Consultant Developer/Analyst Oracle, Forms, Reports & PL/SQL (Windows)
My website: Emu Products Plus