Subject Re: [Firebird-Architect] Long strings and approximate indexes
Author Arno Brinkman

> Firebird has what could be called "approximate" indexes. The fact
> that a value appears in an index doesn't guarantee that the value is a
> legitimate part of a result set. The normal case where a value appears
> in the index but not in the result set is that the value is associated
> with a record version that's not appropriate in the current transaction.
> But there are cases where the value in the index is only approximately
> right. Most numeric values are converted to double precision floating
> point for indexing. This allows a the scale - or length - of the field
> to change without invalidating the index. It does, however create a few
> cases where the index key is only approximately the same value as the
> value in the record. Since the index handling code is already set up to
> check the actual value presented against the actual value in the record
> version being retrieved, these mismatches are recognized.
> We may be able to do the same thing with long strings. Firebird 2
> doesn't need to limit the index key at all, just truncate it at the
> maximum length that can be supported for the current page size, after
> the key is built, but before prefix compression.

A side note, the half-page-size (a bit smaller due header in index-page) is the maximum possible as
For FB2 we accept 1/4 page-size keys (there's also a lookup-table in the index in FB2).
Also the key should then be marked as "truncated" so that we don't waste time fetching unneeded
What about compression on the key if the key is larger then X-length?

Arno Brinkman

Firebird open source database (based on IB-OE) with many SQL-99 features :

Support list for Interbase and Firebird users :

Nederlandse firebird nieuwsgroep :

No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.300 / Virus Database: 266.6.4 - Release Date: 7-3-2005