Hi
I am trying to use a Oracle stage for a lookup, instead of creating a hashfile. Could anyone tell me if this would create any performance issues, on the Database side as well as on the Job?
Thanx
Using OCI stage for lookup
Moderators: chulett, rschirm, roy
Re: Using OCI stage for lookup
It depends... If your hash file needs a lot of records (100.000's) and needs a full refresh every run while you only need a couple of 100 rows an OCI lookup on the original data will be much faster than using a hash file.
If you only have a couple of 100 rows in your hash file, it would beat an OCI lookup any time.
When trying to avoid hash files and if the data you want to look up is in the same Oracle database as your primary input, put the lookup in your input sql using "left outer joins". If the data is not in the same database you could e.g. consider a job to first move data to the database where your primary input resides.
Ogmios
If you only have a couple of 100 rows in your hash file, it would beat an OCI lookup any time.
When trying to avoid hash files and if the data you want to look up is in the same Oracle database as your primary input, put the lookup in your input sql using "left outer joins". If the data is not in the same database you could e.g. consider a job to first move data to the database where your primary input resides.
Ogmios
In theory there's no difference between theory and practice. In practice there is.
Re: Using OCI stage for lookup
Thank you for the suggestion
-
ray.wurlod
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
