Re: ora-1450 even though max key length is well below the max

From: Chuck <skilover_nospam_at_bluebottle.com>
Date: Thu, 22 Jun 2006 15:32:58 GMT
Message-ID: <KKymg.10387$1G2.5646@trnddc06>

Maxim Demenko wrote:
> Chuck schrieb:

>> Gints Plivna wrote:
>>>> Single byte character set on both databases - WE8ISO8859P1
>>> Yea but you said that error message gave you 3118. But looking at your
>>> table definition -length of both varcahr2 columns together (2000 +
>>> 2000) is more than 3118.
>>> So at least the error message is completely justifiable.
>>>
>>> Gints Plivna
>>> http://www.gplivna.eu/
>>>
>>
>> You can't determine key length by adding the max defined size of the
>> columns. To determine key length you must query
>> max(length(col1)+length(col2)...) + number of columns.
>>
>> See metalink note # 136158.1.

>
> I would say, you interpet wrong the mentioned note, Gints is right on it.
> SQL*Plus: Release 10.2.0.1.0 - Production on Thu Jun 22 16:59:59 2006
>
> Copyright (c) 1982, 2005, Oracle. All rights reserved.
>
>
> Connected to:
> Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
> With the Partitioning, Oracle Label Security, OLAP and Data Mining
> Scoring Engine options
>
> scott_at_ORA102> show parameter db_block_size
>
> NAME TYPE VALUE
> ------------------------------------ -----------
> ------------------------------
> db_block_size integer 8192
> scott_at_ORA102> create table bs(col1 varchar2(2000),col2
> varchar2(2000),col3 varchar2(2000),col4 varchar2(2000));
>
> Table created.
>
> scott_at_ORA102> create index bs_idx on bs(col1,col2,col3,col4);
> create index bs_idx on bs(col1,col2,col3,col4)
> *
> ERROR at line 1:
> ORA-01450: maximum key length (6398) exceeded
>
>
> scott_at_ORA102>
>
> Best regards
>
> Maxim

You fail to show the pertinent data from the table. Please run this query...

select max(length(col1)+length(col2)+length(col3)+length(col4)) from bs;

Then add to that number the overhead bytes explained in the note. This is how index key lengths have been calculated since I started with version 7.0. Only the overhead bytes have changed due to changes in the ROWID. As I stated in the OP. This *WORKS* on a 9.2.0.5 instance. The exact same DDL, extracted from the 9205 instance and applied to the 9206 instance does not work. Received on Thu Jun 22 2006 - 10:32:58 CDT