Subject Re: SV: [Firebird-Architect] Indexes for big objects
Author Pavel Cisar
Lars B. Dybdahl wrote:
> What about this:
> Instead of putting large texts in table A, you add an integer that points to
> table B, which contains the texts. This way, you can reduce the number of
> texts in the system, saving space.
> In order to find out, if the text already exists in table B, you would do it
> like this:
> select textid from texttable where text=...
> If the texts can be larger than 4096 bytes, such an index would be useful.

Sorry, still can't see why it would be good to have it built in the
system, when you can solve that easily at "application" level, i.e. use
hashes and queries like:

select textid from texttable where texthash=<valuehash>

I think that need for exact match search for long values are quite rare,
and when needed, it could be very easily implemented at application
level using some sort of value substitution technique (like with hash).
Certainly, a built-in hash-computing function for PSQL operations would
be nice engine enhancement.

What many people would really want is quick search for value parts in
long values (STARTING WITH, CONTAINING), not exact match for long value.
And this type of index is not applicable for this operation.

best regards
Pavel Cisar