Lookup on which stage

[algfr]

Hello guys,

Just a very quick question ?

I need to do a lookup on an Oracle database, is it worth loading the table into a dataset or not ?

If I'm not wrong, it is no more possible to produce an output link out of an OCI stage that has an input link (except reject) which forces me to create an extra job if I need to load a dataset.

Thank you very much

[ShaneMuir]

Have you actually tried?

It is very possible to use an oracle stage directly as a referential input to a lookup.

[algfr]

Have you actually tried?

It is very possible to use an oracle stage directly as a referential input to a lookup.

Hi ShaneMuir,

Yes I have

Just want to know if it takes down performance or not. In other words, is it compulsory to create the datasets to avoid performance drop ?

I heard the wew version creates virtual datasets when lookups on oracle stages occur.

[nagarjuna]

It depends on amount of data that you are handling ...How much data you will have in reference link ??

[algfr]

It depends on amount of data that you are handling ...How much data you will have in reference link ??

My biggest dim is about 150,00 records but the average is below 10,000.

[ray.wurlod]

Unless you are doing a sparse lookup, the data are always loaded into a (virtual) Data Set. You don't have to do anything. This just happens.

Loading into a (physical) Data Set won't change performance at all in the big picture, but may time-shift the cost of loading the data set to an earlier phase of processing. The gain will be slight. A Lookup File Set may be preferable, because that also time-shifts the building of the hash table index on the lookup table (LUT) in memory.

[algfr]

Thank you, this answer my question.

Then I don't have to bother to create a lookup directly from Oracle reference sources.

I suppose this is the main difference with previous versions where it was strongly recommended to insert intermediate datasets.

Good day all