Joiner Transformation in Informatica
The joiner transformation works on a join condition of both input sources, if there’s a match on the port values then Integration service joins these two rows else it’ll pass i.e., it won’t return anything.
Q: What are the various types of joins available?
There are four types of joins available:
- Normal: Returns only matching rows
- Master Outer: all rows from detail source and only matching rows from master source are kept
- Detail Outer: all rows from master source and only matching rows from detail source are kept.
- Full Outer: this join keeps all rows from both sources.
Q: How joiner transformation works?
Data fed to joiner can either be sorted or unsorted.
In case of unsorted joiner, Integration service blocks the detail source and start reading/caches rows from master source. Once it is done, it unblocks the detail source and start reading its rows. So, to improve the performance the run, we prefer smaller source as master source which ultimately needs less space for cache and more space available to process further.
And, In case of sorted joiner, at a time, Integration service caches 100 keys instead of complete master source. Thus it is recommended to use a master source which has fewer duplicate keys, otherwise, more space is required to store same key value which results in performance degradation.