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?
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.
