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

On Nov 27, 5:16 pm, "Charles Hooper" <hooperc2..._at_yahoo.com> wrote:
> 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.

Thanks for designing a query for me!
But still the error persists and as per your comment the common_list_view_test is a lot complicated having a lot of joins. So i have to redesign the common_list_view_test view as even select * from common_list_view_test where pro_id=2 won't work.
Is there any other remedy other than re-designing the query. If yes, please revert back.

Thanks and Regards,

-Sameer Received on Wed Nov 29 2006 - 00:58:45 CST