Re: dbms_utility.get_hash

From: Connor McDonald <hamcdc_at_yahoo.co.uk>
Date: Thu, 21 Oct 2004 00:52:32 +0100 (BST)
Message-ID: <20041020235232.67707.qmail@web86910.mail.ukl.yahoo.com>

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
>

Connor McDonald
Co-author: "Mastering Oracle PL/SQL - Practical Solutions" ISBN: 1590592174 web: http://www.oracledba.co.uk
web: http://www.oaktable.net
email: connor_mcdonald_at_yahoo.com

Coming Soon! "Oracle Insight - Tales of the OakTable"

"GIVE a man a fish and he will eat for a day. But TEACH him how to fish, and...he will sit in a boat and drink beer all day"

___________________________________________________________ALL-NEW Yahoo! Messenger - all new features - even more fun!  http://uk.messenger.yahoo.com

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

Received on Wed Oct 20 2004 - 18:48:05 CDT

Re: dbms_utility.get_hash_value