Re: Blob compare

From: Matthias Hoys <matthias.hoys_at_gmail.com>
Date: Wed, 6 Mar 2013 01:45:17 -0800 (PST)
Message-ID: <b33a1230-6d76-41d6-8103-d9b8f375779d_at_googlegroups.com>

On Tuesday, March 5, 2013 11:22:30 PM UTC+1, Cliff wrote:
> Hi,
>
>
>
> It's been years since I've posted but I'm really stumped on this one.
>
> I have an Oracle BLOB column in a table with about 4.3 million rows. I
>
> have a parent table which links to the 'image table' in a one to many
>
> relation.
>
>
>
>
>
> Parent_tab image_tab
>
> --------------- ---------------
>
> PK column link_id
>
> link_id Blob (containing pdf file)
>
>
>
>
>
> I'm thinking of using 2 cursors in PL/SQL looping though the parent_tab
>
> and then comparing all of the associated blobs to each other. (I
>
> think it's dbms_lob.compare or something like that )
>
>
>
> Is this a logical way to go ? I'm nervous about starting down the
>
> PL/SQL path ( as I'm very rusty) and then find that is the wrong tool.
>
>
>
> TIA,
>
> Cliff

Have you tried using hashing to compare the BLOBs to each other? You can do this with the DBMS_CRYPTO package, and you don't need PL/SQL for it. This is an example (for 10gR2):

select dbms_crypto.HASH(blobcontent,2),count(*) from blobs group by dbms_crypto.HASH(blobcontent,2) order by count(*) desc;

Matthias Received on Wed Mar 06 2013 - 10:45:17 CET