This discussion is archived
7 Replies Latest reply: Jul 24, 2009 3:26 AM by 707125 RSS

hash value

707125 Newbie
Currently Being Moderated
I've created this view but get the same hash value on each row.......is it something obvious..

DROP VIEW t1_view;

CREATE OR REPLACE FORCE VIEW t1_view (
refno,
hash_value
)
AS
SELECT refno,
(SELECT DBMS_UTILITY.get_hash_value ('select refno from testpan3',
100000,
POWER (2, 30)
)
FROM DUAL)
hash_value
FROM testpan3;

e.g. output looks like this

REFNO HASH_VALUE
80045252 56486401
80032014 56486401
  • 1. Re: hash value
    ToonKoppelaars Employee ACE
    Currently Being Moderated
    Not sure what you are trying to achieve.
    But will this help?
    CREATE OR REPLACE FORCE VIEW t1_view (refno,hash_value)
    AS
    SELECT refno,
           DBMS_UTILITY.get_hash_value (refno,100000,POWER (2, 30)) hash_value
    FROM testpan3;
  • 2. Re: hash value
    21205 Oracle ACE Director
    Currently Being Moderated
    you're hashing the same string for each row

    This is what you're hashing:
    'select refno from testpan3'
    it's a string, not a query
  • 3. Re: hash value
    707125 Newbie
    Currently Being Moderated
    thanks chaps.........i thought it was obvious...oops. However i'm getting the same hash_value for different refnos, for example..........

    REFNO     HASH_VALUE

    50961     157868538
    50961     157868538
    196451     157868538
    42702     178030323
    208915     178030323
    87310     196649118
    166677     196649118
    46646     310563049
    418556     310563049
    204766     397948107
    57278     397948107
  • 4. Re: hash value
    94799 Explorer
    Currently Being Moderated
    Compare http://en.wikipedia.org/wiki/Hash_function with http://en.wikipedia.org/wiki/Perfect_hash_function.

    Normal hash functions do not guarantee the absence of collisions.
  • 5. Re: hash value
    MichaelS Guru
    Currently Being Moderated
    You might also try
    select ora_hash(refno)  .......
    when you are on 10g.
  • 6. Re: hash value
    707125 Newbie
    Currently Being Moderated
    thanks but how can I implement this perfect hash function in oracle
  • 7. Re: hash value
    BluShadow Guru Moderator
    Currently Being Moderated
    You'd be better off using the HASH function of the DBMS_CRYPTO package:

    http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_crypto.htm#i1002022

    Which provides better uniqueness. ;)

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points