Re: Execute oexec(): ORA-01467: sort key too long

If I recall correctly this restriction is removed in later versions of Oracle or the limit has been raised.
Don't know anymore specifics though.

Anjo.

Kris Austin-Murray wrote:

>
>disregard the first SELECT sent. was given the wrong query. *this* ugly
>query is the one that fails both in the reporting tool and in sqlplus
>(makes sense now):
>
>SELECT
>FORM_CD,
>SUBMIT_DT,
>MAX(DISTINCT DECODE(Name, '01_First_Name', Value, NULL)) First_Name,
>MAX(DISTINCT DECODE(Name, '02_Last_Name', Value, NULL)) Last_Name,
>MAX(DISTINCT DECODE(Name, '03_Street_Address', Value,
>NULL)) Street_Address,
>MAX(DISTINCT DECODE(Name, '04_City', Value, NULL)) City,
>MAX(DISTINCT DECODE(Name, '05_State', Value, NULL)) State,
>MAX(DISTINCT DECODE(Name, '06_Zip_Code', Value, NULL)) Zip_Code,
>MAX(DISTINCT DECODE(Name, '07_Email', Value, NULL)) Email,
>MAX(DISTINCT DECODE(Name, '08_REF', Value, NULL)) Referred_By,
>MAX(DISTINCT DECODE(Name, '09_Newsletter', Value, NULL)) Newsletter,
>MAX(DISTINCT DECODE(Name, '10_Q1', Value, NULL)) Q1,
>MAX(DISTINCT DECODE(Name, '11_Q2', Value, NULL)) Q2,
>MAX(DISTINCT DECODE(Name, '12_Q3', Value, NULL)) Q3,
>MAX(DISTINCT DECODE(Name, '13_Q4', Value, NULL)) Q4,
>MAX(DISTINCT DECODE(Name, '14_Q5', Value, NULL)) Q5,
>MAX(DISTINCT DECODE(Name, '15_Q6', Value, NULL)) Q6,
>MAX(DISTINCT DECODE(Name, '16_Q7', Value, NULL)) Q7,
>MAX(DISTINCT DECODE(Name, '17_Q8', Value, NULL)) Q8,
>MAX(DISTINCT DECODE(Name, '18_Q9', Value, NULL)) Q9,
>MAX(DISTINCT DECODE(Name, '19_Q10', Value, NULL)) Q10,
>MAX(DISTINCT DECODE(Name, '20_Q11', Value, NULL)) Q11,
>MAX(DISTINCT DECODE(Name, '21_Q12', Value, NULL)) Q12,
>MAX(DISTINCT DECODE(Name, '22_Q13', Value, NULL)) Q13,
>MAX(DISTINCT DECODE(Name, '23_Q14', Value, NULL)) Q14,
>MAX(DISTINCT DECODE(Name, '24_Q15', Value, NULL)) Q15,
>MAX(DISTINCT DECODE(Name, '25_Q16', Value, NULL)) Q16,
>MAX(DISTINCT DECODE(Name, '26_Q17', Value, NULL)) Q17,
>MAX(DISTINCT DECODE(Name, '27_Q18', Value, NULL)) Q18,
>MAX(DISTINCT DECODE(Name, '28_Q19', Value, NULL)) Q19,
>MAX(DISTINCT DECODE(Name, '29_Q20', Value, NULL)) Q20,
>MAX(DISTINCT DECODE(Name, '30_Q21', Value, NULL)) Q21,
>MAX(DISTINCT DECODE(Name, '31_Q22', Value, NULL)) Q22
>FROM value, entry
>WHERE value.entry_id = entry.entry_id AND entry.form_cd =
>'allure_beautyballot2002_mps'
>GROUP BY FORM_CD, SUBMIT_DT, value.entry_id
>/
>
>
>SQL> @run_for_robert.sql
>FROM value, entry
> *
>ERROR at line 35:
>ORA-01467: sort key too long
>
>
>terrible query. i know.
>
>i found this on metalink:
>
>64K Restriction
> ~~~~~~~~~~~~~~~
> The 64K restriction is a maximum internal buffer size for sort
> keys. This can affect any sort operation, including join keys
> used in sort-merge-join operations. For example, if a statement
> joins on a number of VARCHAR2(4000) columns , or group by on
> a number of VARCHAR2(4000) columns then this limit can be hit.
> In most cases the limit is hit at run time and so is data
> dependent, but some operations do use a maximum data size.
>
>is there any solutions out there?
>
>thanks!
>kris
>
>
>
>
>On Fri, 2 Aug 2002, Kris Austin-Murray wrote:
>
>>hi
>>
>>this error is being generated in an admin report tool. the query is the
>>following:
>>
>>SQL> SELECT value.name, value.value, entry.submit_dt
>> 2 FROM value, entry WHERE value.entry_id = entry.entry_id AND
>> 3 entry.form_cd = 'glamour_injeanious_mps';
>>
>>
>>here are the global settings for sqlplus:
>>
>>SQL> show maxdata
>>maxdata 6000
>>SQL> show arraysize
>>arraysize 1
>>
>>this began occuring when we change one field from 2000 characters, to
>>4000:
>>
>>SQL> desc value
>> Name Null? Type
>> ----------------------------------------- ----------------------------
>> ENTRY_ID NOT NULL NUMBER(10)
>> NAME NOT NULL VARCHAR2(100)
>> VALUE VARCHAR2(4000)
>>
>>the total # of records in value:
>>
>>SQL> select count(*) from value;
>>
>> COUNT(*)
>>----------
>> 16408559
>>
>>i've played with setting the arraysize, maxdata without luck. this query
>>does return results from within sqlplus, but not for the reporting tool,
>>which sits on sun solaris 2.5 box with 4 GIG of memory.
>>
>>we're running Oracle8i Enterprise Edition Release 8.1.6.0.0 on sun solaris
>>2.6
>>
>>i've searched metalink and found a doc (Note:1012366.6) that explains the
>>ORA-1467 error. still i am without a solution.
>>
>>any help out there?
>>
>>thank you!
>>kris
>>
>>
>>+-----+------+-----+------+-----+-----+-----+------+------+------+-----+
>>kris austin-murray ... senior database manager, ocp ... advance internet
>> www.advance.net ... kaustin_at_advance.net ... 201-459-2805
>>+-----+------+-----+------+-----+-----+-----+------+------+------+-----+
>>"Darkness cannot drive out darkness; only light can do that. Hate
>>cannot drive out hate; only love can do that." - Martin Luther King, Jr.
>>
>>
>>
>
>+-----+------+-----+------+-----+-----+-----+------+------+------+-----+
>kris austin-murray ... senior database manager, ocp ... advance internet
> www.advance.net ... kaustin_at_advance.net ... 201-459-2805
>+-----+------+-----+------+-----+-----+-----+------+------+------+-----+
>"Darkness cannot drive out darkness; only light can do that. Hate
>cannot drive out hate; only love can do that." - Martin Luther King, Jr.
>
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Anjo Kolk
  INET: anjo_at_oraperf.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).