How to implement ora_hash (seedable hash that divi

Question

I've looked at the wikipedia article on Hash tables but it doesn't seem to say anything about how to implement a hash table that can distribute entries of arbitrary SQL datatype evenly into n buckets.

Can anyone point me in the direction of documentation or existing source code on this topic?

Answer 1

I believe you are talking about a perfect hash function. Oracle's ORA_HASH function is not a perfect hash function.

http://en.wikipedia.org/wiki/Perfect_hash_function

As close as you will get to what you seem to want is an associative array. Oracle has those. Start playing with this example:

set serverout on size 10000
DECLARE
cursor foo 
is 
  select distinct fld1,fld2,fld9  from sometable;

type t is table of foo.%ROWTYPE
  index by varchar2;   -- change the index to an int if you want

myarray t; -- myarray is a table of records -- whatever foo returns

BEGIN
  for x in foo
  loop
      -- index using the first column of the fetched row  "fld1":
      myarray(x.fld1)=x;  -- assign the rowtype to the table of records.      
  end loop;

END;
/  

Note: an associative array is built on a hashtable, the above example uses fld1 as the hash key. So the above will only work if as you describe, perfect hashing, if and only if fld1 is a unique field. That is what the distinct in in there to do. It is never always required.