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

From: Kris Austin-Murray <kaustin_at_advance.net>
Date: Fri, 02 Aug 2002 10:28:42 -0800
Message-ID: <F001.004AA45F.20020802102842@fatcity.com>

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: Kris Austin-Murray
  INET: kaustin_at_advance.net

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).

Received on Fri Aug 02 2002 - 13:28:42 CDT