RE: ORA01467: sort key too long error

From: Powell, Mark <mark.powell2_at_hp.com>
Date: Fri, 8 Jan 2010 16:09:34 +0000
Message-ID: <7C4BF3B32B80CC44AE37D31B172415933A5E8198B7_at_GVW1337EXC.americas.hpqcorp.net>

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
To: oracle-l_at_freelists.org
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

--
http://www.freelists.org/webpage/oracle-l

Received on Fri Jan 08 2010 - 10:09:34 CST