Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Do 'sort key too long' error depends on values supplied to query?

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:16:37 -0800
Message-ID: <1164629797.043648.312930@n67g2000cwd.googlegroups.com>


Charles Hooper wrote:
> 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.

One final note, based on the name "common_list_view_test" it appears that this may be a view that is hiding a lot of complex SQL. The source of the problem may be in that view.

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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US