This discussion is archived
2 Replies Latest reply: Aug 9, 2012 3:21 AM by Helios-GunesEROL RSS

ORA-01467 sort key too long.

Augustine Vinish Newbie
Currently Being Moderated
Hi Team ,

While I am excueting the below query ,its working on oracle 10g but its not working on oracle 9i .

Its throwing an error *"ORA-01467 sort key too long."*

Query
SELECT ARZ_LOC_ID,

MAX(DECODE(R,1,ARZ_DOC_THPY1)) ARZ_DOC_THPY1

*,MAX(DECODE(R,2,ARZ_DOC_THPY1)) ARZ_DOC_THPY2*

*,MAX(DECODE(R,3,ARZ_DOC_THPY1)) ARZ_DOC_THPY3*

*,MAX(DECODE(R,4,ARZ_DOC_THPY1)) ARZ_DOC_THPY4*

*,MAX(DECODE(R,5,ARZ_DOC_THPY1)) ARZ_DOC_THPY5*

*,MAX(DECODE(R,6,ARZ_DOC_THPY1)) ARZ_DOC_THPY6*

*,MAX(DECODE(R,7,ARZ_DOC_THPY1)) ARZ_DOC_THPY7*

*,MAX(DECODE(R,8,ARZ_DOC_THPY1)) ARZ_DOC_THPY8*

*,MAX(DECODE(R,9,ARZ_DOC_THPY1)) ARZ_DOC_THPY9*

*,MAX(DECODE(R,10,ARZ_DOC_THPY1)) ARZ_DOC_THPY10*

*,MAX(DECODE(R,11,ARZ_DOC_THPY1)) ARZ_DOC_THPY11*

*,MAX(DECODE(R,12,ARZ_DOC_THPY1)) ARZ_DOC_THPY12*

*,MAX(DECODE(R,13,ARZ_DOC_THPY1)) ARZ_DOC_THPY13*

*,MAX(DECODE(R,14,ARZ_DOC_THPY1)) ARZ_DOC_THPY14*

*,MAX(DECODE(R,15,ARZ_DOC_THPY1)) ARZ_DOC_THPY15*

*,MAX(DECODE(R,16,ARZ_DOC_THPY1)) ARZ_DOC_THPY16*

*,MAX(DECODE(R,17,ARZ_DOC_THPY1)) ARZ_DOC_THPY17*

*,MAX(DECODE(R,18,ARZ_DOC_THPY1)) ARZ_DOC_THPY18*

*,MAX(DECODE(R,19,ARZ_DOC_THPY1)) ARZ_DOC_THPY19*

*,MAX(DECODE(R,20,ARZ_DOC_THPY1)) ARZ_DOC_THPY20*

*,MAX(DECODE(R,21,ARZ_DOC_THPY1)) ARZ_DOC_THPY21*

*,MAX(DECODE(R,22,ARZ_DOC_THPY1)) ARZ_DOC_THPY22*

*,MAX(DECODE(R,23,ARZ_DOC_THPY1)) ARZ_DOC_THPY23*

*,MAX(DECODE(R,24,ARZ_DOC_THPY1)) ARZ_DOC_THPY24*

*,MAX(DECODE(R,25,ARZ_DOC_THPY1)) ARZ_DOC_THPY25*

*,MAX(DECODE(R,1,NANO)) NANO_1*

*,MAX(DECODE(R,2,NANO)) NANO_2*

*,MAX(DECODE(R,3,NANO)) NANO_3*

*,MAX(DECODE(R,4,NANO)) NANO_4*

*,MAX(DECODE(R,5,NANO)) NANO_5*

*,MAX(DECODE(R,6,NANO)) NANO_6*

*,MAX(DECODE(R,7,NANO)) NANO_7*

*,MAX(DECODE(R,8,NANO)) NANO_8*

*,MAX(DECODE(R,9,NANO)) NANO_9*

*,MAX(DECODE(R,10,NANO)) NANO_10*
*,MAX(DECODE(R,11,NANO)) NANO_11*

*,MAX(DECODE(R,12,NANO)) NANO_12*

*,MAX(DECODE(R,13,NANO)) NANO_13*

*,MAX(DECODE(R,14,NANO)) NANO_14*

*,MAX(DECODE(R,15,NANO)) NANO_15*
*,MAX(DECODE(R,16,NANO)) NANO_16*

*,MAX(DECODE(R,17,NANO)) NANO_17*

*,MAX(DECODE(R,18,NANO)) NANO_18*

*,MAX(DECODE(R,19,NANO)) NANO_19*

*,MAX(DECODE(R,20,NANO)) NANO_20*
*,MAX(DECODE(R,21,NANO)) NANO_21*

*,MAX(DECODE(R,22,NANO)) NANO_22*

*,MAX(DECODE(R,23,NANO)) NANO_23*

*,MAX(DECODE(R,24,NANO)) NANO_24*

*,MAX(DECODE(R,25,NANO)) NANO_25*
*,MAX(DECODE(R,1,DESCR)) DESCR_1*

*,MAX(DECODE(R,2,DESCR)) DESCR_2*

*,MAX(DECODE(R,3,DESCR)) DESCR_3*

*,MAX(DECODE(R,4,DESCR)) DESCR_4*

*,MAX(DECODE(R,5,DESCR)) DESCR_5*

*,MAX(DECODE(R,6,DESCR)) DESCR_6*

*,MAX(DECODE(R,7,DESCR)) DESCR_7*

*,MAX(DECODE(R,8,DESCR)) DESCR_8*

*,MAX(DECODE(R,9,DESCR)) DESCR_9*

*,MAX(DECODE(R,10,DESCR)) DESCR_10*

*,MAX(DECODE(R,11,DESCR)) DESCR_11*

*,MAX(DECODE(R,12,DESCR)) DESCR_12*

*,MAX(DECODE(R,13,DESCR)) DESCR_13*

*,MAX(DECODE(R,14,DESCR)) DESCR_14*

*,MAX(DECODE(R,15,DESCR)) DESCR_15*

*,MAX(DECODE(R,16,DESCR)) DESCR_16*

*,MAX(DECODE(R,17,DESCR)) DESCR_17*

*,MAX(DECODE(R,18,DESCR)) DESCR_18*

*,MAX(DECODE(R,19,DESCR)) DESCR_19*

*,MAX(DECODE(R,20,DESCR)) DESCR_20*

*,MAX(DECODE(R,21,DESCR)) DESCR_21*

*,MAX(DECODE(R,22,DESCR)) DESCR_22*

*,MAX(DECODE(R,23,DESCR)) DESCR_23*

*,MAX(DECODE(R,24,DESCR)) DESCR_24*

*,MAX(DECODE(R,25,DESCR)) DESCR_25*

FROM (SELECT ARZ_LOC_ID,ARZ_DOC_THPY1,PRIORITY_LEVEL,

ROW_NUMBER() OVER(PARTITION BY ARZ_LOC_ID ORDER BY 1) R,

GEO_ID,POSTCODE,STREET,BSNR, nano,

DESCR  FROM (

SELECT ARZ_LOC_ID,ARZ_DOC_THPY1,PRIORITY_LEVEL,R,

GEO_ID,POSTCODE,STREET,BSNR, nano,

dense_rank() over(PARTITION BY arz_loc_id ORDER BY nano) tt

*, lead(nano) over(PARTITION BY arz_loc_id ORDER BY nano, priority_level) tt_1*

*, DESCR*

FROM (

SELECT

ARZ_LOC_ID,ARZ_DOC_THPY1,PRIORITY_LEVEL,R,

GEO_ID,POSTCODE,STREET,BSNR

*, SUBSTR(NANOSEGMENT,1,INSTR(NANOSEGMENT,';',1)-1) nano*

*, SUBSTR(NANOSEGMENT,INSTR(NANOSEGMENT,';',1,2)+1) DESCR*

FROM TMP_ARZ_VALID_DATA

*)*

*) WHERE (nano!=tt_1 OR tt_1 IS NULL ) AND nano IS NOT NULL*

*) GROUP BY ARZ_LOC_ID*


Thanks in Advace .
Regards,
Augustine
  • 1. Re: ORA-01467 sort key too long.
    Fran Guru
    Currently Being Moderated
    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.

    Has both databases (10g and 11g) same structure on this schema? same indexes for example?
  • 2. Re: ORA-01467 sort key too long.
    Helios-GunesEROL Oracle ACE
    Currently Being Moderated
    Hi;

    Please see:

    Recieving ORA-1467 sort key too long when using "View Hot Solutions" [ID 1033373.1]

    How To Fix "Ora-1467" Instead Of Increase Db_block_size [ID 952074.1]

    ORA-1467 Sort Key Too Long [ID 957805.1]

    Regard
    Helios

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points