When would you use Joiner transformation
-
If you want to join two tables in same database
-
If the Order By clause needs to be changed
-
If you want to join two tables on different Oracle databases
-
If you want to join table and flat file
Joiner transformation is specifically used to join data from heterogeneous sources like a table and a flat file, or two different database systems. If both tables are in the same database, it's more efficient to use a SQL Join with Source Qualifier rather than Joiner transformation. Joiner adds overhead and should be avoided when database-level joins are possible.
The Joiner transformation in Informatica PowerCenter is used specifically to combine data from heterogeneous sources that can't be joined with a simple SQL join — the classic example being a relational table and a flat file (or two files, or sources on different database types). Since a flat file has no SQL join capability, the Joiner transformation is required to merge it with table data inside the mapping. Joining two tables in the same database is better done with a SQL override/join in the Source Qualifier; changing an ORDER BY doesn't need a Joiner at all; joining two Oracle databases could sometimes use a database link/SQL override instead, but the textbook "must-use" case for Joiner is combining a table with a flat file.