ORA01467: sort key too long error

From: Saad Khan <saad4u_at_gmail.com>
Date: Fri, 8 Jan 2010 10:58:54 -0500
Message-ID: <76b3d4e31001080758k17005c7cs39396bc16188e23e_at_mail.gmail.com>

Hi DBAs,

One of my developers is facing a issue with his code, when among other columns, he added one more column in the query which used to run fine before that. Its getting following error:

Connecting to the database DEV Datamart.

*ORA-01467: sort key too long*


ORA-06512: at line 2

Process exited.

Disconnecting from the database DEV Datamart.

This error says :
*Cause:*A DISTINCT, GROUP BY, ORDER BY, or SET operation requires a sort key
longer than that supported by Oracle. Either too many columns or too many group functions were specified in the SELECT statement.*Action:*Reduce the number of columns or group functions involved in the operation.

The total number of columns here are 217. The developer insists that he needs all columns and group functions.


I checked in metalink doc 350969.1 which says


When Database blocksize is 2k and the table resides in an 8k-blocksize tablespace then complex
query fails with ORA-1467 SORT KEY TOO LONG


The problem is caused because the temporary tablespace is of 2k-blocksize, where as the sort keys
are longer than 2k

I recreated the temp tablespace with the same block size as database which is 4096 but it didnt help.

I tried another workaround creating a view on the base of this query and then try but it failed as well with the same error.

Can anyone help please?


Received on Fri Jan 08 2010 - 09:58:54 CST

Original text of this message