Dmitry Yemanov dimitr@... [firebird-support] wrote:

> IIRC, unicode derived case/accent insensitive collations use
> six bytes per character encoding. This gives us 2010 bytes
> which is pretty near the 1/4 page size. Given that some
> overhead should be taken into account, the key length may in
> fact overflow the limit.

> I'm not absolutely sure this is your case, but it could be.

That fits. For example I can create an expression index of the
same (335 character) size, and that works, so it must be the
multi-segment overhead that is killing it.


On a test database with page size 4096 I just tried these:

CREATE INDEX myindex
ON testtable COMPUTED BY
(CAST(afield || 'a' AS VARCHAR(169)) COLLATE WIN1252_NOCASE);

that one ((169 * 6) + 9 = 1023) works.


CREATE INDEX myindex
ON testtable COMPUTED BY
(CAST(afield || 'a' AS VARCHAR(170)) COLLATE WIN1252_NOCASE);

that one ((170 * 6) + 9 = 1029) fails with:
"key size exceeds implementation restriction for index".


Which seems to confirm what you've said.


thanks
--
Geoff Worboys
Telesis Computing Pty Ltd