Performance issues for OCI lookup

Su · Post by Su » Mon Jan 13, 2003 1:02 pm

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

ray.wurlod · Post by **ray.wurlod** » Mon Jan 13, 2003 3:52 pm

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.

chulett · Post by **chulett** » Mon Jan 13, 2003 5:02 pm

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

vmcburney · Post by **vmcburney** » Mon Jan 13, 2003 10:42 pm

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