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
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>
Even as way back as version 7, group by never implied sorting...
ReplyDeleteSQL> 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
Dear Connor,
ReplyDeleteThank you for taking the time to comment on my blog.
Kind Regards,
Andrew