Re: Do 'sort key too long' error depends on values supplied to query?

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: 27 Nov 2006 04:14:14 -0800
Message-ID: <1164629654.501989.136140@h54g2000cwb.googlegroups.com>

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

http://www.p2p.wrox.com/archive/oracle/2002-10/14.asp "The following is the interpretation of ora-01467.

ORA-01467 sort key too long

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.

Looking to your query, it seems that it attempts to do too many things at a
same time depending on the data contained in base tables, which might be a
possible reason for Oracle to pose this error. I suggest you to split up
your operations or more suitably use a stored procedure to process output in
a separate table."

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

WHERE
  CL.CHE_ID != 14
  AND CL.CHE_ID=CI. M_CHE_ID(+)
  AND (
    CL.USE_ID = 299
    OR CL.ISS_REC = 299
    OR CI. M_CHE_ID IS NOT NULL
    )
  AND CL.PRO_ID = 2
ORDER BY
  CL.ISS_DAT,
  CL.ISSUE_STRING; Note in the above the that I specified a left outer join between COMMON_LIST_VIEW_TEST and the inline view. By changing the OR portion of the WHERE clause to include "CI. M_CHE_ID IS NOT NULL", we achieve the equivalent of the "OR CHE_ID IN" syntax of your original query.

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Mon Nov 27 2006 - 06:14:14 CST