Compare BLOB against other BLOBs in Oracle database

Question

How to check if there is a duplicate of a blob in a database. I have documents table in the database, however I'm not sure how to check if one of them has a duplicate. I know you're supposed to use dbms_lob.compare function but after reading a few threads and articles on the topic I still do not understand this.

Documents table has document_id column and and data(blob) column. How can I possibly check if document_id = 1 has a duplicate?

How many rows, how much data, and how often are you needing to do this? If there's a lot, dbms_lob.compare will be slower the more rows there are; it might be better to compute a hash and look for duplicates on that instead. — Jeffrey Kemp
– Jeffrey Kemp, Commented Dec 8, 2017 at 5:57

kfinity · Accepted Answer · 2017-12-06 14:40:49Z

16

See other similar answers, like this one. "If dbms_lob.compare(lob1, lob2) = 0, they are identical."

select *
from documents dup
join documents orig on orig.document_id = 1
    and dup.document_id <> orig.document_id
where dbms_lob.compare(orig.blob, dup.blob) = 0;

answered Dec 6, 2017 at 14:40

kfinity

9,1411 gold badge16 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Compare BLOB against other BLOBs in Oracle database

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related