New OWB 11gR2 feature: Dynamic Lookups in Row-Based Mappings
By antonio romero on Nov 10, 2010
Some of the changes in OWB 11gR2, like ODI knowledge module support, are pretty obvious; others are harder to spot. One new feature that has lurked in the tall grasses until now is the new support for dynamic lookups (as our friends at Informatica called them) in the revamped lookup operator. This was alluded to in the OWB 11gR2 New Features list and covered in the documentation but deserves more attention than it got.
When you use dynamic lookup, if a lookup table is changed during the execution of a mapping, the lookups from that point on pick up any changes. To accomplish this, we implemented a couple of subtle changes in the language support of the lookup operator.
In 11gR2 the key lookup operator supports both SQL and PL/SQL. In the past, only SQL was supported. In the doc there is a table defining for the operators the languages that each supports (11gR2 is here, 11gR1 is here - check the lookup operator in table...subtle, maybe too subtle.)
When used in row-based PL/SQL, the lookup operator can now be moved out of the SQL cursor boundary. This becomes clearer when you look at the (somewhat contrived) exampled below.
In the above figure, REF_DATA is the hidden lookup table within the REF_LOOKUP operator. Each row extracted from SRC_DATA will go through the REF_LOOKUP, SPLITTER, then get inserted into REF_DATA (when missed in lookup) and TGT_DATA.
This mapping will behave differently depending on the code generation mode you pick.
- In set-based mode, the behavior is as before-- any changes to REF_DATA are not picked up during mapping execution.
- If you configure the code generation mode and runtime default operating mode for the mapping to be row based, the behavior changes:
In row-based mode, each row extracted from SRC_DATA will go through the REF_LOOKUP, SPLITTER, then get inserted into REF_DATA (when missed in lookup) and TGT_DATA. All these are done before the next row from SRC_DATA is fetched. So the next row will be able to access any new lookup data previous rows have inserted into REF_DATA. Conversely a row from SRC_DATA will NOT be able to access any new lookup data to be generated by future rows. Note that the ordering of rows coming out of SRC_DATA becomes very important-- carefully consider the extraction order from SRC_DATA and add a sort operator right after SRC_DATA to ensure the desired behavior.
- You get another variation if you configure the operating mode of the mapping as row-based target only:
Here the cursor is extended to include everything except the target tables, as shown below.
In this case, the REF_LOOKUP operator will not be able to access any new data inserted in REF_DATA table because REF_DATA is only read when the cursor is opened. You can see this in the generated code:
So that's a quick dump of what’s behind the dynamic lookup in 11gR2. OWB lets you pick the right tradeoff between maximum performance (in set-based mode) and more flexible behavior (in row-based mode).