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: Sameer <dolpheen_at_gmail.com>
Date: 29 Nov 2006 20:17:17 -0800
Message-ID: <1164860237.527971.190550@80g2000cwy.googlegroups.com>

On Nov 29, 5:14 pm, "Charles Hooper" <hooperc2..._at_yahoo.com> wrote:
> Anand Rao wrote:
> > On Nov 29, 11:58 am, "Sameer" <dolph..._at_gmail.com> wrote:
> > > 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
> > Hi,
>
> > It mostly is a block size problem. you may be using a block size that
> > is small to fit that large row that you are sorting.
>
> > have a look at the size of your row.
>
> > anandAnand may have been able to identify the source of the problem. Take a
> look at Metalink document ID Note:18870.1, which describes the error
> and what steps to take to correct the problem.
>
> Does the common_list_view_test view use a SELECT DISTINCT? If so,
> could you re-write it to remove the DISTINCT clause?
>
> Charles Hooper
> PC Support Specialist
> K&M Machine-Fabricating, Inc.

Thanks Sir,
I have rewritten (with the help of seniors) the common_list_view_test view which do have a DISTINCT clause to remove the same and now the query is working fine without any error and a lot faster. That query was missing some joins which i have to recollect. -Sameer Received on Wed Nov 29 2006 - 22:17:17 CST

Original text of this message

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