Helen Borrie napsal(a):

>At 02:32 PM 4/06/2004 +0000, you wrote:
>
>
>>I have this table, FILES with a field FILENAME of type varchar(300),
>>character set win1250, collation coll_slov, and when I try to create
>>an ordinary index on it, I get this error:
>>
>>SQL> create index files_filename on files(filename);
>>Statement failed, SQLCODE = -607
>>
>>unsuccessful metadata update
>>-key size too big for index FILES_FILENAME
>>
>>I'm trying it on firebird 1.5 on win32. My question is: what is this
>>and how can I get rid of it? :) The same happens when the field is
>>declared with width 100, but not with width 50. Database page size iz
>>4096.
>>
>>
>
>The maximum size for an index key is 253 bytes. This maximum gets
>progressively reduced as you "load up" the attributes of the key: multiple
>columns, multi-byte character sets and, in your case, a character set that
>"eats" a lot of extra bytes AND a collation sequence that eats even more.
>
>Ivan Prenosil has made an on-line calculator for you to calculate whether
>you can form an index from a particular mix of character attributes, see
>http://www.volny.cz/iprenosil/interbase/ip_ib_indexcalculator.htm
>
>/heLen
>
>
>

Is it possible to overcome the limit with creating an extra column with
a truncated version of the field that gets inserted/updated using a
trigger and can be indexed due to its length?

Or - better said - would it be practical? Has anybody ever used this and
had success, i.e. those particular queries were faster enough to justify
this scheme? (of course, there are many unique cases that might or might
not benefit from using an index even if it's an "ordinarily" indexed
column. I'm aware of this :)

You could break up the name into a hiearchical structure, BTW. Again,
depends on your demands...

Jakub Hegenbart