RE: dbms_utility.get_hash_value

I would just like to point out that Oracle has something known as a hash
cluster that might be of use/benefit as the data store with solving the end
customer problem.

HTH -- Mark D Powell --

-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Connor McDonald
Sent: Wednesday, October 20, 2004 7:53 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: Re: dbms_utility.get_hash_value


Well, hashing does imply that uniqueness is an 'aim' not a 'guarantee'.

Here's a little routine that demos a hash table of size 'n', and put
collisions at indices
starting at 'n+1' with a simple linked list arrangement.  Not pretty but
reasonably effective

SQL> set serverout on size 999999
SQL> declare
  2    type ENTRY is record (
  3       val  varchar2(30),
  4       nxt  number );
  5
  6    type HASH_TABLE is table of ENTRY
  7       index by binary_integer;
  8
  9    h HASH_TABLE;
 10
 11    hash_size constant number := 800;
 12
 13    hv number;
 14    overflow number := 0;
 15
 16  begin
 17    for i in ( select object_name
 18               from   all_objects where rownum < 1000 ) loop
 19      hv :=     dbms_utility.get_hash_value(i.object_name,1,hash_size);
 20      if h.exists(hv) then
 21        overflow := overflow + 1;
 22        loop
 23          if h(hv).nxt = -1 then
 24             h(overflow+hash_size).val := i.object_name;
 25             h(overflow+hash_size).nxt := -1;
 26             h(hv).nxt := overflow+hash_size;
 27             exit;
 28          else
 29             hv := h(hv).nxt;
 30          end if;
 31        end loop;
 32      else
 33         h(hv).val := i.object_name;
 34         h(hv).nxt := -1;
 35      end if;
 36    end loop;
 37
 38    for i in 1 .. hash_size loop
 39      if h.exists(i) then
 40        dbms_output.put_line('IDX: '||lpad(i,6));
 41        hv := i;
 42        loop
 43          exit when h(hv).nxt = -1;
 44          dbms_output.put_line('- '||h(hv).nxt);
 45          hv := h(hv).nxt;
 46         end loop;
 47      else
 48        dbms_output.put_line('IDX: '||lpad(i,6)||' empty');
 49      end if;
 50    end loop;
 51  end;
 52  /
IDX:      1
IDX:      2
- 899
- 909
- 1097
IDX:      3
IDX:      4
- 1037
IDX:      5
- 954
IDX:      6
- 1079
- 1196
IDX:      7 empty
IDX:      8
IDX:      9
- 961
IDX:     10 empty
IDX:     11
IDX:     12
- 1107
IDX:     13 empty
IDX:     14
- 889
- 930
IDX:     15
- 1160
IDX:     16
- 1111
IDX:     17 empty
IDX:     18 empty
IDX:     19 empty
IDX:     20 empty
IDX:     21 empty
IDX:     22
IDX:     23
- 848
- 949
...
...

IDX:    790
IDX:    791 empty
IDX:    792
IDX:    793
IDX:    794 empty
IDX:    795 empty
IDX:    796 empty
IDX:    797 empty
IDX:    798
- 1083
- 1218
IDX:    799
IDX:    800

PL/SQL procedure successfully completed.

 --- Jose Manuel Quesada <jose.manuelquesada@xxxxxxxxx> wrote: 
> Could anybody tell me under which circunstances different inputs to
> dbms_utility.get_hash_value can lead to the same output.
> 
> The reason is that I work with a 8.1.7.4 database and need to use
> PLSQL tables with the key based on string values.
> 
> So, as the table in indexed by binary integer, I convert the strings
> to integers through dbms_utility.get_hash_value.
> 
> I am so afraid of duplicating keys for different strings that I always
> writing comparison snippets to verify that the key is not being used
> by a different string.
> 
> Is this an exagerated measure?
> 
> Thanks in advance.
> 
> Jose M. Quesada

--
http://www.freelists.org/webpage/oracle-l

Other related posts: