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

 

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>

ORA-01446

This was tested on Oracle 11.2. First I created a table and inserted 2 identical values in it:

SQL> create table tab1 (col1 number)

  2  /

 

Table created.

 

SQL> insert into tab1 values (1)

  2  /

 

1 row created.

 

SQL> insert into tab1 values (1)

  2  /

 

1 row created.

SQL>

Then I looked at the rowids of the values I had inserted:

SQL> select rowid, col1 from tab1

  2  /

 

ROWID                    COL1

------------------ ----------

AAAmpvAAEAAAACLAAA          1

AAAmpvAAEAAAACLAAB          1

SQL>

Next I created a view and checked that it referred back to the underlying table and picked up the correct rowids again: 

SQL> create view view1

  2  as select col1 from tab1

  3  /

 

View created.

 

SQL> select rowid, col1 from view1

  2  /

 

ROWID                    COL1

------------------ ----------

AAAmpvAAEAAAACLAAA          1

AAAmpvAAEAAAACLAAB          1

SQL>

Finally I created a view to show the distinct values in the table. This worked as expected too: 

SQL> create view view2

  2  as select distinct col1 from tab1

  3  /

 

View created.

 

SQL> select col1 from view2

  2  /

 

      COL1

----------

         1

SQL> 

But when I tried to look at the rowids in this view, I got an ORA-01446 instead. The rowids in the table itself gave details of each row's physical location in the database. So when I passed these rows through a DISTINCT into a view, there were two occurrences of the value 1 in the table giving one DISTINCT value in the view. Oracle then did not know which row's rowid to associate with the value displayed: 

SQL> select rowid, col1 from view2

  2  /

select rowid, col1 from view2

       *

ERROR at line 1:

ORA-01446: cannot select ROWID from, or sample, a view

with DISTINCT, GROUP BY, etc.

 

SQL>

ORA-01732

This was tested on Oracle 11. Sometimes you can update a view and sometimes you can't. In view1 below, there is a one to one correspondence between the rows in the view and the rows in the base table. If you update the view, Oracle goes to the underlying table and makes the appropriate changes there: 
 

SQL> create table table_list

  2  as select * from dba_tables

  3  /

 

Table created.

 

SQL> create view view1

  2  as select * from table_list

  3  /

 

View created.

 

SQL> update view1 set owner = 'ANDREW'

  2  /

 

3063 rows updated.

 

SQL> select owner, count(*) from table_list

  2  group by owner

  3  /

 

OWNER                            COUNT(*)

------------------------------ ----------

ANDREW                               3063

 

SQL> delete table_list

  2  /

 

3063 rows deleted.

 

SQL> insert into table_list

  2  select * from dba_tables

  3  /

 

3064 rows created.

SQL>

However, view2 includes a group by statement. Each row in this view has data collected from one or more rows in the base table. If you try to update it, Oracle has no idea how to carry any changes through to the base table so it generates an error message:

 

SQL> create view view2

  2  as select owner, count(*) number_present

  3  from table_list

  4  group by owner

  5  /

 

View created.

 

SQL> update view2 set number_present = 0

  2  /

update view2 set number_present = 0

       *

ERROR at line 1:

ORA-01732: data manipulation operation not legal on

this view

 

SQL>

Sort Key too Long

This is an example of the following error:

UNIX > oerr ora 01467
01467, 00000, "sort key too long"
// *Cause:
// *Action:
UNIX >

It was tested on Oracle 9. The maximum sort key you can use depends on the block size, which is 8192 in this database:

SQL> col value format a5
SQL> select value from v$parameter
  2  where name = 'db_block_size'
  3  /

VALUE
-----
8192

SQL>

First create a table with 3 columns of 4000 characters each, followed by a number. Use the NOPRINT clause to stop SQL*Plus printing these long columns:

SQL> col a noprint
SQL> col b noprint
SQL> col c noprint
SQL> col substr(c,1,92) noprint
SQL> col substr(c,1,93) noprint
SQL> create table andrew
  2  (a varchar2(4000),
  3   b varchar2(4000),
  4   c varchar2(4000),
  5   x number)
  6  /

Table created.

SQL>

Fill columns a, b and c with repeated characters and put a small number in column x.

SQL> insert into andrew values (rpad('?',4000,'?'),
  2  rpad('*',4000,'*'),rpad('!',4000,'!'),1)
  3  /

1 row created.

SQL> insert into andrew values (rpad(';',4000,';'),
  2  rpad('£',4000,'£'),rpad('^',4000,'^'),2)
  3  /

1 row created.

SQL>

Sort the rows. The sort key is 12000 characters long but the order by statement seems to handle the columns individually:

SQL> select x from andrew order by a,b,c
  2  /

         X
----------
         2
         1

SQL>

However, the group by statement appears to concatenate the columns in the sort key. The maximum permitted sort key length in this case is 4000 + 4000 + 92 = 8092:

SQL> select a,b,substr(c,1,92),sum(x)
  2  from andrew
  3  group by a,b,substr(c,1,92)
  4  /

    SUM(X)
----------
        2
        1

SQL> select a,b,substr(c,1,93),sum(x)
  2  from andrew
  3  group by a,b,substr(c,1,93)
  4  /
from andrew
    *
ERROR at line 2:
ORA-01467: sort key too long

SQL>

Group By ... Having

This post shows 2 pieces of SQL. The first has a group by, which I have already shown in other posts. The second restricts the output to show groups of 50 or more. You are not allowed to use a where clause to filter the output from a group by statement, you must use having instead, as shown in the example:  

SQL> select object_type, count(*)
  2  from dba_objects
  3  group by object_type
  4  /

OBJECT_TYPE          COUNT(*)
------------------ ----------
CLUSTER                    10
CONSUMER GROUP              4
DATABASE LINK              12
DIRECTORY                   1
EVALUATION CONTEXT          1
FUNCTION                  129
INDEX                     992
INDEX PARTITION            25
LIBRARY                    62
LOB                        52
MATERIALIZED VIEW           2
OPERATOR                    2
PACKAGE                   321
PACKAGE BODY              313
PROCEDURE                 183
QUEUE                       8
RESOURCE PLAN               3
SEQUENCE                  168
SYNONYM                  1782
TABLE                     830
TABLE PARTITION            27
TRIGGER                    90
TYPE                      480
TYPE BODY                  21
VIEW                     2414

25 rows selected.

SQL> select object_type, count(*)
  2  from dba_objects
  3  group by object_type
  4  having count(*) > 50
  5  /

OBJECT_TYPE          COUNT(*)
------------------ ----------
FUNCTION                  129
INDEX                     992
LIBRARY                    62
LOB                        52
PACKAGE                   321
PACKAGE BODY              313
PROCEDURE                 183
SEQUENCE                  168
SYNONYM                  1782
TABLE                     830
TRIGGER                    90
TYPE                      480
VIEW                     2414

13 rows selected.

SQL>

optimizer_features_enable

In an earlier post, I pointed out that, starting with Oracle 10, you need to include an ORDER BY after a GROUP BY if you want the output to be in order.

 

While I was researching this on the Internet, I came across several people who had discovered unexpected behaviour after an upgrade. With proper testing, this should not happen but, if it does, you can try using optimizer_features_enable as a temporary workaround.

 

The example below demonstrates this. It runs a query in an Oracle 10 database with a GROUP BY but no ORDER BY. The output is not in order. The query is then rerun with optimizer_features_enable set to 9.2.0. This time the output is in order.

 

Setting this parameter disables all new features provided by an upgrade, not just the ones you do not like, so you should implement a long term solution ASAP:

 

TEST10 > sqlplus /

 

SQL*Plus: Release 10.2.0.3.0 - Production on Mon Sep 5 13:58:05 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> col value format a30

SQL> select value from v$parameter

  2  where name = 'optimizer_features_enable'

  3  /

 

VALUE

------------------------------

10.2.0.3

 

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                   742

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> alter session

  2  set optimizer_features_enable = '9.2.0'

  3  /

 

Session altered.

 

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(*)

------------ ----------

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                   742

X                    23

Y                     3

_                    68

b                     1

 

27 rows selected.

 

SQL>