Get_Hash_Value_collisions (merged) [message #388399] |
Tue, 24 February 2009 10:30  |
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 #388411 is a reply to message #388409] |
Tue, 24 February 2009 10:49   |
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 #388417 is a reply to message #388413] |
Tue, 24 February 2009 11:37   |
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 #388428 is a reply to message #388424] |
Tue, 24 February 2009 12:53   |
 |
Michel Cadot
Messages: 68733 Registered: March 2007 Location: Saint-Maur, France, https...
|
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 #388441 is a reply to message #388436] |
Tue, 24 February 2009 14:22   |
 |
BlackSwan
Messages: 26766 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   |
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 #388592 is a reply to message #388493] |
Wed, 25 February 2009 09:47   |
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.
|
|
|
|
|