hash file issue

[xli]

Hi, Folks

I have hash file issue need your advice :

I have a daily running job populate table t1 and t2 with the same 4 millions records. All data will be kept for 3 month, hence the estimated number of records for these tables will grow up to about 240 millions.

t2 is used as lookup to check the source date duplicate. I am really worried about the performance issue, I had never handle such a big table as lookup. I wonder if I am supposed to use oracle table as lookup or, create a hash table (what a big hash table!). or, is there any other way may help improve the lookup performance ?

Thanks in advance

Regards

xiong

[kduke]

xiong

Don't do it. Do the lookup an Oracle plugin. The trade off is in the time it takes to build the hash file versus how much time you save doing the lookup. Do it both ways and let us know.

Kim.

Kim Duke
DwNav - ETL Navigator
www.Duke-Consulting.com

[jseclen]

Hi Xiong,

You must considerate the follow items:

1) The lookup table must to have a primary key (or index) with the statistics updated to improve performance lookup.
2) Don't use hashed files, is very hard to build a big file .. for the numbers of records will take many hours to create...
3) use the fields that belong of the index of the lookup table.

I hope this help.

Miguel Seclen
Lima - Peru

[JDionne]

I may be out of my leage here but...why are you using a dupication of the info as a look up. why not just create a lookup of just the keys. it will be much faster to look up than looking up against the full record. give that a thought
Jim


quote:Originally posted by jseclen
[br]Hi Xiong,

You must considerate the follow items:

1) The lookup table must to have a primary key (or index) with the statistics updated to improve performance lookup.
2) Don't use hashed files, is very hard to build a big file .. for the numbers of records will take many hours to create...
3) use the fields that belong of the index of the lookup table.

I hope this help.

Miguel Seclen
Lima - Peru