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
Performance issues for OCI lookup
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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
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