DSXchange

Hi,
Datastage version 4.2
Database - Oracle 8.1.7

We need to lookup a big table (about 2 million rows) based on a free-text field - The source could also have upto or more than 2 million rows. Since creating a hashfile for the lookup table is not possible due to the free text field being 2000 chars long, we are using an OCI8 stage lookup.
Obviously,this takes forever - any ideas on how to speed things up?

Any help would be greatly appreciated.

Thanks,
Su

Depending on the actual form of the lookup, about the only thing I could suggest would be an index based on the key words that are likely to be sought.

Just out of curiousity -

Have you looked into utilizing a 'Text Index'? There is a form of it available in 8i (CONTEXT type) while more options are available in 9i. You need to use specific query syntax, but they are meant to greatly improve the process of finding free-form text inside a large varchar.

-craig

It may help to give a couple examples of the values being matched.

You could put both tables in the same database and then let Oracle join them together. This should be faster than a DataStage row by row join.

regards
Vincent