Re: Blob compare

From: <Cliff>
Date: Wed, 6 Mar 2013 06:20:13 -0500
Message-ID: <2013030606201371805-_at_news.giganews.com>

On 2013-03-06 09:45:17 +0000, Matthias Hoys said:

> 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

No I have not tried but thank you for the idea. I'll research it right away.

Cliff Received on Wed Mar 06 2013 - 12:20:13 CET