I have been thinking of creating a brand new Knowledge Module which will make an Incremental Update without using any UPDATE/DELETE SQL statements, only with inserts to increase performance. Initially, I was thinking that there should be a second integration table, but when I started scrawling to figure out flow diagram and how Knowledge Module works, I realized that there is no need for a second integration table.
If you are using Oracle database which your target table existing, this Knowledge Module can fit your needs. Let me try to explain how it works;
1. Drops and Creates I$ flow table (as usual)
2. Inserts the missing records (new records on source) to I$ flow table (if INSERT_NEW_ROWS option is set to true).
3. Inserts the matching records (existing records on source) to I$ flow table (if UPDATE_EXISTING_ROWS option is set to true).
4. Inserts the non-matching records (deleted records on source) to I$ flow table (if NOT_DELETE_NON_EXISTING_ROWS option is set to true).
5. Wrap them up and truncate & insert target table from flow table and drop flow table (if DELETE_TEMPORARY_OBJECTS option is set to true).
6. Analyze target (if ANALYZE_TARGET option is set to true).
To find the matching records, we need a set of key columns (depending on tables structure), which is set as “Update Key” on Target Datastore of our interface and can be defined in model. There could be some alternative keys that sometimes you don’t need to use primary key (that is already created in database). Especially when you are making a Surrogate Key Transformation. So, you can create your own key (this key should not be stored in database, only in ODI usage) by right clicking “Constraints” on table (in ODI Model) and add an “Alternate Key”, then add desired columns to it. This key, you created, can be selected as “Update Key” in this Knowledge Module by changing “Update Key” in Target Properties in Target Datastore section (it is a combo box).
Click the links to download the IKM Oracle Incremental Update (Updateless) v1 Knowledge Module from my Google drive or from here IKM Oracle Incremental Update Updateless(v1) (don’t forget to change the surname of the file to xml after downloading).
Remember that, use these files at your own risk, backup your current environment and this Knowledge Module is not supported by Oracle.