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

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.

anand

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
Received on Wed Nov 29 2006 - 01:17:31 CST