Comparision of Hash and OCI lookup

[NBALA]

Hi,

Hope this might be discussed earlier, when searching I could not get right answer.

Situation : Consider a fact table contains 7 dimension and need to lookup all 7 dimensions.

While comparing hask lookup and OCI lookup which one will be best choice? and which will give good performance?

Which will more suitable to implement SCD?

If we used hash table, what are the issues can one expect?

Thanks
-NB

[satish_valavala]

Hi Bala,

Do you mean lookup dimension table(s) using OCI stage(s)?
What is the volume of your dimension table(s)?

Best practice in performing lookup is, using Hashed Files. By using Hashed Files, you always write lookup data into Hashed Files, where as with OCI stages ( OCI SQL / user-defined SQL) you will directly access physical DB tables.

Best Regards
VS

[kduke]

Either works. It is a matter of preferrence. It is also a performance issue. Hashed files are faster but they take some time to build. If you add up the overall times for both then that should be your deciding factor. The fewer rows in the dimension the faster it loads. So I would say until you get to over 100,000 rows hashed files are faster then you need to check your performance of both.

[deepak.shanthamurthy]

you might also want to consider the update frequency of your dimensions as a factor

[NBALA]

Thanks all !

The dimension has around 100,000 rows(in one of the Dim), I tried to use OCI but getting poor performance like 8 rows/sec. It started with 121 rows/sec then it started gradually decreasing the number of rows.. fact is having 1.3 million to load.

I will try with hash and check the performance.

-NB

[lcallif2]

You could have this done in the source stage, outerjoining the 7 dimension tables. Thsi will allow the Database to perform all the lookup work, which it can perform as LOT better then the Job via a hash or OCI lookup....

Thanks,

Lester


Hope this might be discussed earlier, when searching I could not get right answer.

Situation : Consider a fact table contains 7 dimension and need to lookup all 7 dimensions.

While comparing hask lookup and OCI lookup which one will be best choice? and which will give good performance?

Which will more suitable to implement SCD?

If we used hash table, what are the issues can one expect?

Thanks
-NB[/quote]