ORA01467: sort key too long error
Date: Fri, 8 Jan 2010 10:58:54 -0500
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 "DATAMART.REFRESHCATA_REPORTCARD2STEP2", line 32
ORA-06512: at line 2
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?