| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Execute oexec(): ORA-01467: sort key too long
Kris,
I don't think the query has too many columns in its group by clause, but
there are a lot of group functions in the select list and maybe that is the
problem I'm not sure what the sort key will look like.
But the distinct isn't accomplishing anything - max already implies 1 value.
If removing the distinct doesn't help maybe you can convince the developers to remove the the whole grouping thing (max and group by) and just order the results by the keys. That should provide a solution, although the output will include a row for each key/pseudocolumn combination instead of a single row for each key, hopefully they can find other ways to transpose or pivot results - I think something was just posted here recently.
What are the benefits of this interesting design? Is this one of those one-table two-column databases?
Maybe others will be more helpful,
chaim
Kris Austin-Murray <kaustin_at_advance.net>@fatcity.com on 08/02/2002 02:28:41 PM
Please respond to ORACLE-L_at_fatcity.com
Sent by: root_at_fatcity.com
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> cc:
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'
SQL> @run_for_robert.sql
FROM value, entry
*
terrible query. i know.
i found this on metalink:
64K Restriction
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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: Chaim.Katz_at_Completions.Bombardier.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).Received on Mon Aug 05 2002 - 08:43:32 CDT
 |
 |