RE: dbms_utility.get_hash

From: Powell, Mark D <mark.powell_at_eds.com>
Date: Thu, 21 Oct 2004 11:13:52 -0400
Message-ID: <564DE4477544D411AD2C00508BDF0B6A2133DF5A@usahm018.exmi01.exch.eds.com>

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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Connor McDonald Sent: Wednesday, October 20, 2004 7:53 PM To: oracle-l_at_freelists.org
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_at_gmail.com> 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

Received on Thu Oct 21 2004 - 10:09:36 CDT

RE: dbms_utility.get_hash_value