OraFAQ Forum: SQL & PL/SQL » CREATE CLUSTER - convert a string to a number?

Home » SQL & PL/SQL » SQL & PL/SQL » CREATE CLUSTER - convert a string to a number?

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

CREATE CLUSTER - convert a string to a number? [message #117528]

Wed, 27 April 2005 16:09

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

Report message to a moderator

Re: CREATE CLUSTER - convert a string to a number? [message #117727 is a reply to message #117528]

Fri, 29 April 2005 06:12

Frank Naude
Messages: 4596
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

Report message to a moderator

Re: CREATE CLUSTER - convert a string to a number? [message #117916 is a reply to message #117727]

Sun, 01 May 2005 10:32

theShookster
Messages: 2
Registered: April 2005
Location: USA

Junior Member

Thank you

Report message to a moderator

Re: CREATE CLUSTER - convert a string to a number? [message #187954 is a reply to message #117528]

Wed, 16 August 2006 08:35

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!!!!

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Oct 05 09:22:42 CDT 2025