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

From: Anurag Varma <avoracle_at_gmail.com>
Date: 22 Jun 2006 08:18:03 -0700
Message-ID: <1150989483.746835.212490@p79g2000cwp.googlegroups.com>

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 Received on Thu Jun 22 2006 - 10:18:03 CDT