Re: ORA01467: sort key too long error
- From: "Jaromir D.B. Nemec" <jaromir@xxxxxxxxxxxx>
- To: <nigel.cl.thomas@xxxxxxxxxxxxxx>, "Saad Khan" <saad4u@xxxxxxxxx>
- Date: Sun, 10 Jan 2010 21:04:21 +0100
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> @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> @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
Other related posts:
