Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Do 'sort key too long' error depends on values supplied to query?
Sameer wrote:
> Please have a look at this query (Just given for guessing
> complications).
> This query works and gives output for pro_id=1 and other few values.
> But it fails for value 2.
>
>
> SELECT *
> FROM common_list_view_test
> WHERE che_id != 14
> AND(use_id = 299 OR iss_rec = 299 OR che_id IN
> (SELECT a.m_che_id
> FROM m_chk_pt a, m_role_chk_pt b, m_user c
> WHERE a.m_che_id = b.m_che_id
> AND b.m_rol_id = c.m_rol_id
> AND c.m_use_id = 299))
> AND pro_id = 2
> ORDER BY iss_dat,
> issue_string
>
> For pro_id = 2 it gives error:
> ORA-01467: sort key too long
>
> What do we mean by sort key?
> Is it depends on supplied information and the records to be fetched.
> If the query is going to fetch a lot of records then do this error
> occur?
>
> How to avoid this error and make the query to work got pro_id=2.
>
> -Sameer
You might try a Google search for this error: Oracle sort key too long site:oracle.com
Many articles can be found on asktom.oracle.com
>From another web page
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.
Consider rewriting the SQL statement to use an inline view rather than
a subquery, similar to the following:
SELECT
CL.*
FROM
COMMON_LIST_VIEW_TEST CL,
(SELECT DISTINCT
A.M_CHE_ID
FROM
M_CHK_PT A, M_ROLE_CHK_PT B, M_USER C WHERE A.M_CHE_ID = B.M_CHE_ID AND B.M_ROL_ID = C.M_ROL_ID AND C.M_USE_ID = 299) CI
Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc.
Received on Mon Nov 27 2006 - 06:14:14 CST