Home » SQL & PL/SQL » SQL & PL/SQL » Get_Hash_Value_collisions (merged)
Get_Hash_Value_collisions (merged) [message #388399] Tue, 24 February 2009 10:30 Go to next message
porthole
Messages: 10
Registered: February 2009
Junior Member
I am trying to get a unique key for our currency codes for each date. For example, I am using...

select dbms_utility.get_hash_value(currency_date||currency_code, 0, 2147483647)
from dim_currency

currency_date is a date for every day, and each currency_code has one.

currency_code is a code for each country (we only use 14 currency_codes).

so.... 14 currency_code, 365 days a year, for the last 20 years
= 102200

I should be able to get a unique key and should for several years, right?

I continue to get collisions. I have checked my data for duplicates. Any clues? Thanks!
Re: get_hash_value collisions [message #388402 is a reply to message #388399] Tue, 24 February 2009 10:38 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
Is not "currency_date||currency_code" unique.

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
So we can help you & please be consistent & correct in your postings.
Re: get_hash_value collisions [message #388403 is a reply to message #388399] Tue, 24 February 2009 10:38 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
do NOT cross/multi-post
Re: get_hash_value collisions [message #388405 is a reply to message #388402] Tue, 24 February 2009 10:40 Go to previous messageGo to next message
porthole
Messages: 10
Registered: February 2009
Junior Member
correct, it is unique.
Re: get_hash_value collisions [message #388407 is a reply to message #388399] Tue, 24 February 2009 10:43 Go to previous messageGo to next message
porthole
Messages: 10
Registered: February 2009
Junior Member
I wouldn't have except for the website became unresponsive and I didn't relize it had posted three. Please feel free to close these duplicate messages. Sorry.
Re: get_hash_value collisions [message #388409 is a reply to message #388399] Tue, 24 February 2009 10:44 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>I am trying to get a unique key for our currency codes for each date.
>correct, it is unique.
So, if you want UNIQUE KEY & have unique key, why are you making this more difficult?
Re: get_hash_value collisions [message #388411 is a reply to message #388409] Tue, 24 February 2009 10:49 Go to previous messageGo to next message
porthole
Messages: 10
Registered: February 2009
Junior Member
number data type and consistant data types are more acceptable in a data warehouse environment. I need a key to be a number in my dimension table and in my fact table.

We don't get currency rates until the end of the day, and we get orders throughout the day. Our "late arriving" currency rates need to be assigned to the orders that have already arrived.

I would be nice to be able to "predict" the tomorrow currency key before I get the currency rate so I could put it with my orders in my fact table.

Re: Get_Hash_Value_collisions (merged) [message #388413 is a reply to message #388399] Tue, 24 February 2009 11:14 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>number data type and consistant data types are more acceptable in a data warehouse environment
"more acceptable" than what & why?
My initial reaction to this statement is that somebody has bought into some Urban Legend of database mythology.
Re: Get_Hash_Value_collisions (merged) [message #388417 is a reply to message #388413] Tue, 24 February 2009 11:37 Go to previous messageGo to next message
porthole
Messages: 10
Registered: February 2009
Junior Member
I have been justifying myself from the first of the post. I suppose this is the wrong place to be for data warehousing. I don't need an argument about data warehousing with you. If you want understand data warehouse (and one methodology) users check out http://www.kimballgroup.com/. Data warehouses are built on integer key values and surrogate keys for reasons.

However, I didn't think I had to justify why I wanted to use Oracle. I asked how a function in Oracle would work and I get the runaround.

Let's say you are right and I should continue to use the alphanumberic as my key... fine. So far no "help" was given for the question. I have just received hassle.

I guess, feel free to close this.

Robert
Re: Get_Hash_Value_collisions (merged) [message #388421 is a reply to message #388399] Tue, 24 February 2009 12:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If currency_date is a date make sure your default format contains full date, it is then better to explicitly use TO_CHAR function.

Can you post some of the collisions you have.

Regards
Michel
Re: Get_Hash_Value_collisions (merged) [message #388424 is a reply to message #388421] Tue, 24 February 2009 12:34 Go to previous messageGo to next message
porthole
Messages: 10
Registered: February 2009
Junior Member
I think I understand what you are asking. I have tried to format the date into a 'mmddyyyy' format then concatenate it with the currency code. I have attached a couple of my queries. Here is what I have been looking at.

select dbms_utility.get_hash_value('05252006CAD',37, 2147483647)
from dual

select dbms_utility.get_hash_value('09072008CAD',37, 2147483647)
from dual

So I have my date formatted to_char(date_field,'mmddyyyy')||currency_code.

The above queries I manually put in the values from the results I got from the attached queries.
  • Attachment: hash_sql.txt
    (Size: 0.69KB, Downloaded 123 times)
Re: Get_Hash_Value_collisions (merged) [message #388426 is a reply to message #388399] Tue, 24 February 2009 12:47 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
what are the data types of currency_date & currency_code?
Re: Get_Hash_Value_collisions (merged) [message #388428 is a reply to message #388424] Tue, 24 February 2009 12:53 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I think you may enlarge the input value to decrease the likelyhood of collision, something like:
dbms_utility.get_hash_value(to_char(currency_date,'DDMMYYYY')||currency_code||to_char(currency_date,'YYYYMMDD'), 0, 2147483647)


Regards
Michel

[Updated on: Tue, 24 February 2009 12:53]

Report message to a moderator

Re: Get_Hash_Value_collisions (merged) [message #388436 is a reply to message #388426] Tue, 24 February 2009 14:03 Go to previous messageGo to next message
porthole
Messages: 10
Registered: February 2009
Junior Member
currency_date = date
currency_code = varchar2(3)
Re: Get_Hash_Value_collisions (merged) [message #388441 is a reply to message #388436] Tue, 24 February 2009 14:22 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
porthole wrote on Tue, 24 February 2009 12:03
currency_date = date
currency_code = varchar2(3)


to_number(
to_char(date_field,'yyyymmdd')||
to_char(ascii(substr(currency_code,1,1)))||
to_char(ascii(substr(currency_code,2,1)))||
to_char(ascii(substr(currency_code,3,1)))
)

above should provide unique number or mix & match in any order desired.

[Updated on: Tue, 24 February 2009 14:23]

Report message to a moderator

Re: Get_Hash_Value_collisions (merged) [message #388446 is a reply to message #388441] Tue, 24 February 2009 16:51 Go to previous messageGo to next message
porthole
Messages: 10
Registered: February 2009
Junior Member
Thanks for the solution. I appreciate your help. I will end up using this. However, it still makes me wonder why I can't get the hash values to work. I haven't tried the last suggestion. That will be next.

Thanks again.
Re: Get_Hash_Value_collisions (merged) [message #388493 is a reply to message #388446] Wed, 25 February 2009 00:48 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
What makes you think hashing unique values will result in unique hash-values?
Re: Get_Hash_Value_collisions (merged) [message #388592 is a reply to message #388493] Wed, 25 February 2009 09:47 Go to previous messageGo to next message
porthole
Messages: 10
Registered: February 2009
Junior Member
well, nothing other than an assumption. I realize get_hash_values isn't perfect. However, I just assumed that for the small dataset that I had, 100k-200k records, get_hash_value would be able to churn a unique value if I were allowing it 0 to 2.1 billion possibilities.

But again, I am not 100% how that function works. If you have another suggestion, I am open.


Re: Get_Hash_Value_collisions (merged) [message #388596 is a reply to message #388592] Wed, 25 February 2009 09:58 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
If you convert currency-code to a number, you can simply concatenate that to the date (with format yyyymmdd)
Re: Get_Hash_Value_collisions (merged) [message #388639 is a reply to message #388596] Wed, 25 February 2009 14:25 Go to previous message
porthole
Messages: 10
Registered: February 2009
Junior Member
Correct. And that is exactly what blackswan has done. It seems to be working. Thank you.

Robert
Previous Topic: Query to know the last 5 querys laucnhed (merged)
Next Topic: Find the text in entire database
Goto Forum:
  


Current Time: Fri Dec 09 21:43:30 CST 2016

Total time taken to generate the page: 0.05768 seconds