It will often occur that we need to search a large character string in a relational database, something like an email address, postal address, etc.
For the most part, when we do such searches, we're searching for a sub-string within that large string of characters.
However, for instances where we know we need an equivalency search, we can make things more efficient by generating a hash of the string and then storing that in an indexed column.
This can be done in all 3 of the major RDBMS platforms. Links below:
MSSQL uses the CHECKSUM() function.
DB2 LUW uses the DBMS_UTILITY.GET_HASH_VALUE() function.
Oracle uses the ORA_HASH function.
This article by Jeff Reinhard over at SQLServerCentral.com is what inspired me to look for the similar function in DB2 & Oracle, and it does a very nice job of explaining the use case for this using an e-mail address scenario.
If anyone knows how to do this in postgressql or MySQL, I'd love to have you add a few words or link in the comments. Thanks! :)
1 comment:
I think this is an informative post and it is very useful and knowledgeable. therefore, I would like to thank you for the efforts you have made in writing this article. Bethlehem Web Developers
Post a Comment