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:39:50 GMT
Message-ID: <aRymg.10388$1G2.959@trnddc06>

Anurag Varma wrote:
> Chuck wrote:

>> 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.

>
> Chuck,
>
> For the index creation, oracle will consider the total key length as
> the sum total of defined key lengths.
>
> If max it allows for your case is 3118 bytes, and you have
> defined columns being indexed of total size > 4000 bytes.
>
> That said, 3118 limit seems to be for a 4K block size. Are you sure
> that your block size is 8K?
>
> Here is an example:
>
> Oracle 10.2.0.2 on Linux
>
> SQL> show parameters db_block_size
>
> NAME TYPE VALUE
> ------------------------------------ ----------
> ------------------------------
> db_block_size integer 8192
> SQL> select value from NLS_DATABASE_PARAMETERS where parameter =
> 'NLS_CHARACTERSET';
>
> VALUE
> ---------------------------------------------
> WE8ISO8859P1
>
> SQL> create table Y (a date, b varchar2(4000),c varchar2(4000));
>
> Table created.
>
> SQL> create index Y_IDX on Y (a,b,c);
> create index Y_IDX on Y (a,b,c)
> *
> ERROR at line 1:
> ORA-01450: maximum key length (6398) exceeded
>
>
> I did not even have to insert a single row. The limit is on
> the total of defined column sizes!
>
> Anurag
>

At carumba.

Does anyone around here read? I'll say it one last time... this works in a 9205 instance with the exact same DDL. If as you say it's based on the max defined width of the varchars how does this work on 9205?

desc mytable

 Name                          Null?    Type
 ----------------------------- -------- --------------------
 LOGDATE                       NOT NULL DATE
 URI                                    VARCHAR2(2000)
 REFERRER                               VARCHAR2(2000)
 PAGEVIEWS                              NUMBER(38)

CREATE UNIQUE INDEX MYINDEX_U1 ON MYTABLE (LOGDATE, URI, REFERRER)
COMPRESS 2; Index created. Received on Thu Jun 22 2006 - 10:39:50 CDT