Joining SeqFile and oracle Table

deepbanerji · Post by **deepbanerji** » Thu May 26, 2005 1:55 pm

Hi,
I have to Join a SeqFile and Oracle table without using HashFiles. Is there any way to do this.

Thanks,
Deep

amsh76 · Post by **amsh76** » Thu May 26, 2005 1:59 pm

Use OCI stage as look up..but with high volume it will hurt performance.

ranga1970 · Post by **ranga1970** » Thu May 26, 2005 2:42 pm

Why this
'without hash file' what way it is going to hurt you..
instead it will enhance your performance..

imagin situation, where you are trying to lookup 1million records and you are hitting databse 1 million times, where as you could have done it in single queery...

ray.wurlod · Post by **ray.wurlod** » Thu May 26, 2005 5:08 pm

For entire-key-based lookups a memory-cached hashed file will always be faster than an Oracle table. A disk-based hashed file is possibly still faster than an Oracle table, and definitely if the Oracle instance is on a separate machine from the DataStage server.

On the other hand, where you have a sparse lookup (only touching a few distinct values), then caching in the query engine may ensure that the database connection - particularly if local - may be quicker, as none of the non-referenced keys needs to be loaded (which would be the case if a hashed file were used).