Re: Function-based index on LOBs?

Frank Foss wrote:
> Oracle 9.2.0.4 on Win2000 Advanced Server
>
> I tried to find the info on Tahiti, but came up short on what
> Oracle-supplied functions can be used in function-based indexes.
>
> Here's the idea:
> We're currently using an in-house application that stores files on
the
> filesystem, and generating SHA1 hashes per file. This checksum is
used
> to check for duplicates.
> We're looking at inserting the files in the database instead, as
BLOBs.
> To speed up the search for duplicate/identical files, the SHA1 (or
MD5,
> or RMD160 or other message digests) will be stored in the same record

> with the file.
>
> Is it possible to create a function-based index that will create the
> message digest (SHA1/MD5) upon insert of the bytestream?
>
> Does Oracle provide such a digest function? Is it possible to call an

> external function? ( "openssl dgst -md5 <something>" )
> Or just bite the dust and compute the value and insert it ourselves?
>
> Any hints/tips/tricks/pointers to The Fine Manual appreciated
> Foz

The only place the message digest will appear using a function-based index is in the index itself, presuming you can create such a function-based index. A trigger, on the other hand, can generate your digest value upon insert and populate a column in the same record with the calculated value. A plain-vanilla index on that column should then suffice.

I would seriously consider creating such a trigger; you have fewer restrictions on a trigger, and many more functions you can use to create your digest.

My two cents.

David Fitzjarrell Received on Wed Dec 22 2004 - 22:30:53 CST