Home » SQL & PL/SQL » SQL & PL/SQL » CREATE CLUSTER - convert a string to a number?
CREATE CLUSTER - convert a string to a number? [message #117528] Wed, 27 April 2005 16:09 Go to next message
theShookster
Messages: 2
Registered: April 2005
Location: USA
Junior Member
Hello there,
I am taking DB course which Oracle is great part of it. We had a debate with the professor about an issue. Whenever we execute the command 'CREATE CLUSTER' , Oracle converts a string into a number (via an internal Oracle function).
I would like to know how does Oracle use the hashing method to convert a string to a number.
Thanks,

Shooki Grasiani
Re: CREATE CLUSTER - convert a string to a number? [message #117727 is a reply to message #117528] Fri, 29 April 2005 06:12 Go to previous messageGo to next message
Frank Naude
Messages: 4502
Registered: April 1998
Senior Member
Hi,

I guess you are referring to hash clusters as all clusters don't necessarily use hashing.

If so, you can supply a hash function (HASH IS ...) that outputs a number of scale 0. For example: NUMER_COLUMN * length(STRING_COLUMN).

If you omit the HASH IS clause, Oracle uses an internal hash function. The workings of this "internal hash function" are not documented and very little is known about it.

Here is an example of using the internal hash function:

SQL> VAR hash NUMBER
SQL> EXEC :hash := dbms_utility.get_hash_value('test string', base=>10000, hash_size=>2048);

PL/SQL procedure successfully completed.

SQL> PRINT :hash

      HASH
----------
     11481


Best regards.

Frank
Re: CREATE CLUSTER - convert a string to a number? [message #117916 is a reply to message #117727] Sun, 01 May 2005 10:32 Go to previous messageGo to next message
theShookster
Messages: 2
Registered: April 2005
Location: USA
Junior Member
Thank you
Re: CREATE CLUSTER - convert a string to a number? [message #187954 is a reply to message #117528] Wed, 16 August 2006 08:35 Go to previous message
dvamsikrishna
Messages: 4
Registered: August 2006
Junior Member
Problem similar one but hashing is required, I'm not at all familiar with Hashing...

Hi All:

Sample Data
Table A
Store ID: Name, Store_Latest_ID
121, Kansas, 1101
1101, Dallas, 1200
1200, Irvine, Null
141, Gering, 1462
1462, Scott, Null
1346,Calif,0
1455, NewJersey,0

Table B is a materialized aggregated view
Store ID: Sales_Qty, Month
121, $1200, 1
1101, $1400, 2
1200, $ 1800, 3
141, $500, 1
1462, $1500, 2
1346,$1500, 1
1455, $2000,1


Expected Output

Store ID: Sales_Qty, Month
1200, $1200, 1
1200, $1400, 2
1200, $1800, 3
1462, $500, 1
1462, $1500, 2
1346,$1500, 1
1455, $2000,1

I want to pass the function values to the materialized view so as to update all the store id's with the Latest Store ID, For Example consider this
In Table A (Source table where we have Latest Store ID)
Store ID: Name, Store_Latest_ID
121, Kansas, 1101
1101, Dallas, 1200
1200, Irvine, Null

the storeid 121 has changed to 1101 and again it changed to 1200 so all the rows in the materialized view which has store id's of 121 and 1101 should be changed to 1200

If the Store Latest ID is 0 then there is no change in the store id of the materialized view.

Thanks A Lot!!!!


Previous Topic: how to delete the users
Next Topic: When I use weak REF CURSOR to use BULK COLLECTION
Goto Forum:
  


Current Time: Thu Dec 08 06:27:23 CST 2016

Total time taken to generate the page: 0.17895 seconds