Wednesday, May 28, 2014

GROUP BY and ORDER BY

In 1997, I was sent on a 1 day Introduction to Oracle course. The lecturer said that if you used a GROUP BY, you could not assume that the results would be returned in order. If you wanted to make sure, you should include an ORDER BY as well. From a theoretical point of view, he was correct as an RDBMS is not obliged to return results in any particular order unless you tell it to do so. But from a practical point of view, a simple way to group data is to sort it first. Up to and including version 9, Oracle seems to have used this method:
 
TEST9 > sqlplus /
 
SQL*Plus: Release 9.2.0.7.0 - Production on Fri Sep 2 18:04:16 2011
 
Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
 
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.7.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.7.0 - Production
group by, Oracle 10, Oracle 9, order by,
 
SQL> @group_by
SQL> set pages 100
SQL> col first_letter format a12
SQL> select substr(segment_name,1,1) first_letter,
  2  count(*)
  3  from dba_segments
  4  group by substr(segment_name,1,1)
  5  /
 
FIRST_LETTER   COUNT(*)
------------ ----------
1                     1
A                   118
B                    92
C                    98
D                    43
E                    17
F                    51
G                    22
H                    39
I                   293
J                     5
K                     7
L                   115
M                   116
N                     2
O                    29
P                    52
Q                    49
R                   170
S                   319
T                   121
U                    54
V                    16
W                    11
X                    14
_                   119
 
26 rows selected.
 
SQL>
 
This is no longer the case and, starting with Oracle 10, if you want to see the output of a GROUP BY in order, you have to include an ORDER BY as well:
 
TEST10 > sqlplus /
 
SQL*Plus: Release 10.2.0.3.0 - Production on Fri Sep 2 18:15:01 2011
 
Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.
 
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
 
SQL> @group_by
SQL> set pages 100
SQL> col first_letter format a12
SQL> select substr(segment_name,1,1) first_letter,
  2  count(*)
  3  from dba_segments
  4  group by substr(segment_name,1,1)
  5  /
 
FIRST_LETTER   COUNT(*)
------------ ----------
P                   161
O                    37
V                    83
D                   157
_                    68
M                   448
N                    98
W                   779
Q                    40
Y                     3
C                   135
I                   550
B                   120
F                    55
U                    20
S                   717
T                   250
A                   316
J                    21
E                   143
L                   178
R                   303
H                   662
K                     9
b                     1
G                     7
X                    23
 
27 rows selected.
 
SQL> select substr(segment_name,1,1) first_letter,
  2  count(*)
  3  from dba_segments
  4  group by substr(segment_name,1,1)
  5  order by 1
  6  /
 
FIRST_LETTER   COUNT(*)
------------ ----------
A                   316
B                   120
C                   135
D                   157
E                   143
F                    55
G                     7
H                   662
I                   550
J                    21
K                     9
L                   178
M                   448
N                    98
O                    37
P                   161
Q                    40
R                   303
S                   717
T                   250
U                    20
V                    83
W                   779
X                    23
Y                     3
_                    68
b                     1
 
27 rows selected.
 
SQL>


2 comments:

Anonymous said...

Even as way back as version 7, group by never implied sorting...

SQL> drop table T;

Table dropped.

SQL> create table T (x int not null, y int not null);

Table created.

SQL> create index IX on T ( y,x);

Index created.

SQL> insert into T values (1,5);

1 row created.

SQL> insert into T values (8,2);

1 row created.

SQL> exec dbms_stats.gather_table_stats('','T');

PL/SQL procedure successfully completed.

SQL> alter session set "_gby_hash_aggregation_enabled" = false;

Session altered.

SQL> select x,y, count(*)
2 from t
3 group by x,y;

X Y COUNT(*)
---------- ---------- ----------
8 2 1
1 5 1

Cheers,
Connor

Andrew Reid said...

Dear Connor,

Thank you for taking the time to comment on my blog.

Kind Regards,

Andrew