Checking uniqueness on real time application

Question

On a real time messaging application, I want to control if incoming message is unique. For this purpose, I am planning to insert a hash of incoming message as unique key in db and check if I get unique key exception. (ORA-00001 in oracle). Is this an efficient way or is there a better way to consider for this case ? For ones who want to know, program is written in java and as a db we use oracle.

Answer 1

If you're trying to get around the performance problem of uniqueness tests on very large strings, then this is a decent way of achieving it, yes.

You might need a way to deal with hash collisions, though, as the presence of a unique key would prevent different messages having the same hash from loading. One way would be to check for existing matching hashes and do a comparison test against the full text of the message. It would keep your index size down as you'd index on the hash not the message text, but Ii would not be completely foolproof as two identical messages could be loaded by different sessions if the timing was exactly right (or wrong, depending on your perspective).