RE: ORA01467: sort key too long error
Date: Fri, 8 Jan 2010 16:09:34 +0000
Saad, one possible solution if the problem is truely the size of the sort key and the sort key column values are too large would be to re-create the database using an 8K block size instead of 4K. This would increase the size of the sort key Oracle can handle. It is however an expensive solution.
A couple of things you can check about the design
Are the key columns definded as varchar2 instead of char(n)? Are number data types being used to hold numeric values instead a char data types? Are date values stored as date data types?
In other words are the right data types being used?
HTH -- Mark D Powell --
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Saad Khan Sent: Friday, January 08, 2010 10:59 AM
Subject: ORA01467: sort key too long error
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 Cause
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?