what is the maximum length of the fields that comprise an index

By: Borland Staff

Abstract: The general rule here is that the total length of all fields must not be over 200 bytes.

Problem:
When I create an index with large varchar/char fields I get an error:

      unsuccessful metadata update
      -key size too big for index 

Solution:
The maximum length of an index is depends on whether the index is a
multisegment index or not.  The total key length is 256, however there
is some overhead, the size of which greatly depends on how many fields
in the index.  

For single field indices the general rule is that the max length of an
index fields is ~250 bytes.  As you have probably seen this will vary
for the different datatypes (can create an index on a varchar(252)
field).

For multisegment indices the overhead for the index is greatly
increased.  The general rule here is that the total length of all fields
must not be over 200 bytes.  For example:

        v1 varchar(192);
        v2 varchar(184);
        i1 integer;
        i2 integer;
        create index (v1, i1) will work because the i1 fields is counted as 8
        bytes so total is 200 bytes

        create index (v1, i1, i2) will work for same reason.

Server Response from: ETNASC04