7 Replies Latest reply: Jul 24, 2009 5:26 AM by 707125 RSS

    hash value

    707125
      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
          Toon Koppelaars
          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
            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
              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
                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
                  You might also try
                  select ora_hash(refno)  .......
                  when you are on 10g.
                  • 6. Re: hash value
                    707125
                    thanks but how can I implement this perfect hash function in oracle
                    • 7. Re: hash value
                      BluShadow
                      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. ;)