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-00904

I noticed this in Oracle 11. However, I do not think it is a bug as the same thing happens in Oracle 9. I joined two SELECT * statements with a UNION and added an ORDER BY at the end. This gave me an ORA-00904. It seemed to me that the ORDER BY did not know the names of the columns in the output from the SELECT *:

 

SQL> create table andrew (col1 number)

  2  /

 

Table created.

 

SQL> create table john (col1 number)

  2  /

 

Table created.

 

SQL> select *

  2  from user_tables

  3  where table_name = 'JOHN'

  4  union

  5  select *

  6  from user_tables

  7  where table_name = 'ANDREW'

  8  order by table_name

  9  /

order by table_name

         *

ERROR at line 8:

ORA-00904: "TABLE_NAME": invalid identifier

 

SQL>

 

I found the following workarounds. One of them may be suitable if you hit this problem yourself. I used SELECT TABLE_NAME instead of SELECT *:

 

SQL> select table_name

  2  from user_tables

  3  where table_name = 'JOHN'

  4  union

  5  select table_name

  6  from user_tables

  7  where table_name = 'ANDREW'

  8  order by table_name

  9  /

 

TABLE_NAME

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

ANDREW

JOHN

 

SQL>

 

I removed the ORDER BY. For the purposes of this example, I also added SET HEAD OFF and removed some blank lines from the output:

 

SQL> set head off

SQL> select *

  2  from user_tables

  3  where table_name = 'JOHN'

  4  union

  5  select *

  6  from user_tables

  7  where table_name = 'ANDREW'

  8  /

 

ANDREW

USERS

                               VALID            10

                    1        255          65536

                      1  2147483645

                           YES N

         1          1     N ENABLED

          NO               N N NO  DEFAULT DISABLED NO

NO                  DISABLED YES

                               DISABLED DISABLED

                   NO  NO

 

JOHN

USERS

                               VALID            10

                    1        255          65536

                      1  2147483645

                           YES N

         1          1     N ENABLED

          NO               N N NO  DEFAULT DISABLED NO

NO                  DISABLED YES

                               DISABLED DISABLED

                   NO  NO

 

SQL>

 

I looked at the USER_TABLES view, saw that TABLE_NAME was the first column and used ORDER BY 1 instead:

 

SQL> desc user_tables

Name                       Null?    Type

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

TABLE_NAME                 NOT NULL VARCHAR2(30)

TABLESPACE_NAME                     VARCHAR2(30)

CLUSTER_NAME                        VARCHAR2(30)

IOT_NAME                            VARCHAR2(30)

STATUS                              VARCHAR2(8)

PCT_FREE                            NUMBER

PCT_USED                            NUMBER

INI_TRANS                           NUMBER

MAX_TRANS                           NUMBER

INITIAL_EXTENT                      NUMBER

NEXT_EXTENT                         NUMBER

MIN_EXTENTS                         NUMBER

MAX_EXTENTS                         NUMBER

PCT_INCREASE                        NUMBER

FREELISTS                           NUMBER

FREELIST_GROUPS                     NUMBER

LOGGING                             VARCHAR2(3)

BACKED_UP                           VARCHAR2(1)

NUM_ROWS                            NUMBER

BLOCKS                              NUMBER

EMPTY_BLOCKS                        NUMBER

AVG_SPACE                           NUMBER

CHAIN_CNT                           NUMBER

AVG_ROW_LEN                         NUMBER

AVG_SPACE_FREELIST_BLOCKS           NUMBER

NUM_FREELIST_BLOCKS                 NUMBER

DEGREE                              VARCHAR2(10)

INSTANCES                           VARCHAR2(10)

CACHE                               VARCHAR2(5)

TABLE_LOCK                          VARCHAR2(8)

SAMPLE_SIZE                         NUMBER

LAST_ANALYZED                       DATE

PARTITIONED                         VARCHAR2(3)

IOT_TYPE                            VARCHAR2(12)

TEMPORARY                           VARCHAR2(1)

SECONDARY                           VARCHAR2(1)

NESTED                              VARCHAR2(3)

BUFFER_POOL                         VARCHAR2(7)

ROW_MOVEMENT                        VARCHAR2(8)

GLOBAL_STATS                        VARCHAR2(3)

USER_STATS                          VARCHAR2(3)

DURATION                            VARCHAR2(15)

SKIP_CORRUPT                        VARCHAR2(8)

MONITORING                          VARCHAR2(3)

CLUSTER_OWNER                       VARCHAR2(30)

DEPENDENCIES                        VARCHAR2(8)

COMPRESSION                         VARCHAR2(8)

COMPRESS_FOR                        VARCHAR2(18)

DROPPED                             VARCHAR2(3)

READ_ONLY                           VARCHAR2(3)

 

SQL> select *

  2  from user_tables

  3  where table_name = 'JOHN'

  4  union

  5  select *

  6  from user_tables

  7  where table_name = 'ANDREW'

  8  order by 1

  9  /

 

ANDREW

USERS

                               VALID            10

                    1        255          65536

                      1  2147483645

                           YES N

         1          1     N ENABLED

          NO               N N NO  DEFAULT DISABLED NO

NO                  DISABLED YES

                               DISABLED DISABLED

                   NO  NO

 

JOHN

USERS

                               VALID            10

                    1        255          65536

                      1  2147483645

                           YES N

         1          1     N ENABLED

          NO               N N NO  DEFAULT DISABLED NO

NO                  DISABLED YES

                               DISABLED DISABLED

                   NO  NO

 

SQL>

I put the UNION inside brackets and put the ORDER BY outside the brackets: 

SQL> select * from

  2  (select *

  3   from user_tables

  4   where table_name = 'JOHN'

  5   union

  6   select *

  7   from user_tables

  8   where table_name = 'ANDREW')

  9  order by table_name

10  /

 

ANDREW

USERS

                               VALID            10

                    1        255          65536

                      1  2147483645

                           YES N

         1          1     N ENABLED

          NO               N N NO  DEFAULT DISABLED NO

NO                  DISABLED YES

                               DISABLED DISABLED

                   NO  NO

 

JOHN

USERS

                               VALID            10

                    1        255          65536

                      1  2147483645

                           YES N

         1          1     N ENABLED

          NO               N N NO  DEFAULT DISABLED NO

NO                  DISABLED YES

                               DISABLED DISABLED

                   NO  NO

 

SQL>

Subquery with Order By?

This was tested on Oracle 11.1.0.6.0 running on Windows XP. Looking through some course notes from 1990 (as you do), I read that you cannot include an order by in a subquery. I'm not sure why you would ever want to do such a thing but I decided to see what happened if you did:

SQL> l
  1  select count(*)
  2  from dba_tables
  3  where table_name not in
  4  (select table_name
  5   from dba_indexes
  6*  order by 1)
SQL> /
 order by 1)
 *
ERROR at line 6:
ORA-00907: missing right parenthesis

SQL> l
  1  select count(*)
  2  from dba_tables
  3  where table_name not in
  4  (select table_name
  5   from dba_indexes
  6*  order by table_name)
SQL> /
 order by table_name)
 *
ERROR at line 6:
ORA-00907: missing right parenthesis

SQL>

It gave me an ORA-00907, as you can see above, which was not especially helpful. I removed the order by and the query ran successfully:

SQL> l
  1  select count(*)
  2  from dba_tables
  3  where table_name not in
  4  (select table_name
  5*  from dba_indexes)
SQL> /

  COUNT(*)
----------
       444

SQL>

However, I have just thought of a special kind of subquery, sometimes called an in-line view, which is allowed to have an order by. Here is an example, suggested by Laurent in the 1st comment below:

SQL> l

  1  select * from

  2  (select sid, a.value/100 CPU_Seconds

  3   from v$sesstat a, v$sysstat b

  4   where a.statistic# = b.statistic#

  5   and name = 'CPU used by this session'

  6   order by a.value desc)

  7* where rownum < 6

SQL> /

       SID CPU_SECONDS

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

        71      401.64

       140      361.04

        10      306.59

         9         306

        43      280.75

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>