RE: ORA01467: sort key too long error
- From: "Powell, Mark" <mark.powell2@xxxxxx>
- To: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- 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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Saad Khan
Sent: Friday, January 08, 2010 10:59 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: ORA01467: sort key too long error
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 "DATAMART.REFRESHCATA_REPORTCARD2STEP2", line 32
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
Symptoms
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?
Thanks,
Khan
Other related posts:
