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>