Hashing strings in Oracle 8i,9i,10g |
Hashing strings in Oracle 8i,9i,10g
David Yahalom | Jan 19, 2009 | Comments (2)
There's an easy way to hash strings in Oracle 8i,9i,10g.
In Oracle 10g you can use the ORA_HASH function which computes a hash value for any given expression. It recieves three arguments:
expr - determines the data for which you want Oracle Database to compute a hash value. You can go wild on the length of this string there isn't any restriction on this.
max_bucket (optional) - determines the maximum bucket value returned by the hash function. You can specify any value between 0 and 4294967295. The default is 4294967295.
seed_value argument (optional) - enables Oracle to produce many different results for the same set of data. Oracle applies the hash function to the combination of expr and seed_value. You can specify any value between 0 and 4294967295. The default is 0.
SQL> select ora_hash('hello world') from dual;
ORA_HASH('HELLOWORLD')
----------------------
1896528268
SQL>
In Oralce 8i and 9i there is not ORA_HASH function. What we can use instead is the DBMS_UTILITY.GET_HASH_VALUE function. It is similar yet different from ORA_HASH .
The GET_HASH_VALUE function takes three mandatory parameters:
name - The string we want converted from string to integer via hash.
base - The base value for the integer used in the hashing algorithm.
hash_size - This is the size of the hash table. The total number of values that are available to the hashing algorithm as conversions from the string inputs. It is recommended that this will be the power of two.
For example, to get a hash value on a string where the hash value should be between 1000 and 3047, use 1000 as the base value and 2048 as the
hash_size
value.
QL> select DBMS_UTILITY.GET_HASH_VALUE('hello world1', 2,1048576) from dual;
DBMS_UTILITY.GET_HASH_VALUE('HELLOWORLD1',2,1048576)
----------------------------------------------------
113894
SQL>
|
|