Re: ORA01467: sort key too long error

From: Jaromir D.B. Nemec <jaromir_at_db-nemec.com>
Date: Sun, 10 Jan 2010 21:04:21 +0100
Message-ID: <5E4DA466AC5B484BBAA66C2E9DAADBA4_at_ADLA>

Hi Saad,

> ORA-01467: sort key too long
> I recreated the temp tablespace with the same block size as database which is 4096 but it didnt help

what is the DB version? 9i?
It seems that the limitation on the blocksize for the "sort key" was lifted somewhere in 10g. See below - both DBs have 8KB blocks.

regards,

Jaromir

9.2 fails on a key ca. 40kB with blocksize 8K

---

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.8.0 - Production

SQL> set autotrace traceonly
SQL> set echo on
SQL> _at_sortKey10
SQL>
SQL> select col1,
  2  col2,
  3  col3,
  4  col4,
  5  col5,
  6  col6,
  7  col7,

  8 col8,
  9 col9,
 10 col10,
 11 count(*) from (
 12 select

 13  rpad('x',3999,'y') col1,
 14  rpad('x',3999,'y') col2,
 15  rpad('x',3999,'y') col3,
 16  rpad('x',3999,'y') col4,
 17  rpad('x',3999,'y') col5,
 18  rpad('x',3999,'y') col6,
 19  rpad('x',3999,'y') col7,
 20  rpad('x',3999,'y') col8,
 21  rpad('x',3999,'y') col9,
 22  rpad('x',3999,'y') col10

 23 from dual
 24 )
 25 group by

 26  col1,
 27  col2,
 28  col3,
 29  col4,
 30  col5,
 31  col6,
 32  col7,
 33  col8,
 34  col9,

 35 col10
 36 ;
from dual

     *
ERROR at line 23:
ORA-01467: sort key too long

10.2 no problem

---

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production With the Partitioning, OLAP and Data Mining options

SQL> set autotrace traceonly
SQL> set echo on
SQL> _at_sortKey10
SQL>
SQL> select col1,
  2  col2,
  3  col3,
  4  col4,
  5  col5,
  6  col6,
  7  col7,

  8 col8,
  9 col9,
 10 col10,
 11 count(*) from (
 12 select

 13  rpad('x',3999,'y') col1,
 14  rpad('x',3999,'y') col2,
 15  rpad('x',3999,'y') col3,
 16  rpad('x',3999,'y') col4,
 17  rpad('x',3999,'y') col5,
 18  rpad('x',3999,'y') col6,
 19  rpad('x',3999,'y') col7,
 20  rpad('x',3999,'y') col8,
 21  rpad('x',3999,'y') col9,
 22  rpad('x',3999,'y') col10

 23 from dual
 24 )
 25 group by

 26  col1,
 27  col2,
 28  col3,
 29  col4,
 30  col5,
 31  col6,
 32  col7,
 33  col8,
 34  col9,

 35 col10
 36 ;

Execution Plan

---

   0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=1)    1 0 SORT (GROUP BY NOSORT) (Cost=2 Card=1)    2 1 FAST DUAL (Cost=2 Card=1)

Statistics

---

          1  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
      41137  bytes sent via SQL*Net to client
        512  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

--
http://www.freelists.org/webpage/oracle-l

Received on Sun Jan 10 2010 - 14:04:21 CST