Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Function-based index on LOBs?

Re: Function-based index on LOBs?

From: <fitzjarrell_at_cox.net>
Date: 23 Dec 2004 11:27:41 -0800
Message-ID: <1103830061.048029.168400@f14g2000cwb.googlegroups.com>

Frank Foss wrote:
> fitzjarrell_at_cox.net wrote:
> > 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.
>
>
> Thank you very much for your input.
> It is probably a better concept.
>
> The question is then turning into:
> Is there a message digest function available in PL/SQL that can
compute
> SHA1 or MD5 values? Again, I could not find references to an existing

> Oracle function.
>
>
>
> >
> > My two cents.
> >
> >
> > David Fitzjarrell
> >
>
> Much appreciated
> Foz

Use the DBMS_OBFUSCATION_TOOLKIT.MD5 function. It requires an input and an output parmeter. the output being the checksum. I believe it will work with your data. but test it before you go off into production.

David Fitzjarrell Received on Thu Dec 23 2004 - 13:27:41 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US