Subject Re: [firebird] Re: SV: [Firebird-Architect] Indexes for big objects
Author Andrew Berg
Oh, please. That is like saying "I can't see why it would be good to
provide stored procedures or integrity constraints when you can
easily solve that at the 'application' level."

I, myself, have run into this situation repeatedly when storing
encrypted data in a database. The fact that I was able to work
around a shortcoming of the underlying system does in no way mean
that we should leave this shortcoming in place. The solution I used
was to index a column containing [ASN1 hash + first 20 chars + last
20 chars] of the long string. So far I have not seen a collision,
and do not expect to, but I am still forced to maintain code to deal
with the case where a collision occurs.


On Jul 8, 2006, at 1:43 AM, Pavel Cisar wrote:

> 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
> IBPhoenix
> Yahoo! Groups Links