ORA-01450: maximum key length (3214) exceeded

hi,

I've a 'create index' problem ...
I need to create an index on VARCHAR2(4000) col. but something is wrong ??

this is what I've done (815):

1. create table tst (a varchar2(4000));
2. create index tst_idx on tst(a);

message:

ERROR at line 1:
ORA-01450: maximum key length (...) exceeded


I've tried on 2 differents db :

-with 8k block:

ERROR at line 1:
ORA-01450: maximum key length (3218) exceeded

-with 2k block:

ERROR at line 1:
ORA-01450: maximum key length (758) exceeded

Same results , what a pity! :(

I've found in the doc. :

'ORA-01450 maximum key length (string) exceeded

Cause:
The combined length of all the columns specified in a CREATE INDEX statement exceeded the maximum index length. The maximum index length varies by operating system. The total index length is computed as the sum of the width of all indexed columns plus the number of indexed columns. Date fields have a length of 7, character fields have their defined length, and numeric fields have a length of 22. Numeric length = (precision/2) + 1. If negative, add +1.

Action: Select columns to be indexed so the total index length does not exceed the maximum index length for the operating system. See also your operating system-specific Oracle documentation.'


But what this exactly means ?

1.what's the 'maximum index length' on NT and on SUN SOLARIS (I use both) in my sample ?

2.My 'maximum index length' is it : 4000(=all indexed columns) + 1(=number of indexed columns) ?

I don't understand what 'maximum index length' is ? :(((

3.And, of course, what can I do to create my index on my varchar2(4000) col. ?
I suppose I can create an index on a varchar2(4000) col, isn't it ?


thanks in advance

1-2:

The maximum key length for your database is approximately 40% of the database block size minus some overhead.

3:

Recreate the DB with a larger block size.

thanks for your advice :)

have you considered alternate index methods, such as CONTEXT?

I have to question whether a 4K index field is useful.

The maximum number of bytes allowed in a column for an index to be created is 3209 in 8i.

Excellent thread!
I have a question: do really this kind of indexes make some improvements? I never thought about something like this, because my business rules never lead to this kind of (specific) situation. But if I'll ever be in this situation ... hm, how big should a column be to make such a index usefull, and how many rows in the table. I think is a dificult situation, to need indexes on a 4XXX chars length column.
Of course, if the dynamic is low, it is very usefull, but if the rows are often updated, I do not know.
Of course, I could be very wrong, but this is why I try to find out...
Any oppinions here?

Quote:

---

Originally posted by tamilselvan
The maximum number of bytes allowed in a column for an index to be created is 3209 in 8i.

---

It is not that simple (besides, the size provided above is not correct anyhow).

The maximum allowed index key length depends on your block size. So the mixumum allowed size reported in ORA-01450 varies, depending on which block size your index is using:

ORA-01450 maximum key length (758) exceeded ->(2K Block)
ORA-01450 maximum key length (1578) exceeded ->(4K block)
ORA-01450 maximum key length (3218) exceeded ->(8K Block)
ORA-01450 maximum key length (6498) exceeded ->(16K Block)

The numbers in parenthesis represent the maximum index key length for that specific block size. Actually, even those numbers are not the theoretical upper limit, because they are calculated based on the default values of PCTFREE and INITRANS. So in theory you could "squeeze" a couple of additional bytes for max key length by reducing the values of PCTFREE and INITRANS below their defaults.

If anybody is interested in how the maximum index key length is computed, there is a document (Doc.Id 136158.1) on Metalink that explains this in details.