I have an requirement as below:
1) 3 source tables: 2 of them are base tables in ORMS and the 3rd table holds the mapping between the RMS IDs and the internal IDS used in our project.
2) Target table: 1 , which is a staging table.
Database : oracle for both source and target
LKM: SQL to Oracle
IKM: SQL Control Append
ODI version: 184.108.40.206
ODI agent : Is running in ORMS
Requirement is to make a Cartesian join between the 2 base tables of RMS and then join the 3rd table to fetch the internal values( or IDs) for each of them. The data is then written into the staging table. From the staging table the data will be written into a flat file.
We are able to complete our development but the issue is with the time the ODI interface is taking to write the data into the staging table. The data is huge, nearly 15 million records. So the interface does take hours before finishing.+
It would be helpful if you can suggest any tuning steps we could use to decrease the processing time.
Use the LKM Oracle to Oracle using database link if possible, this will be much faster than SQL to SQL. Also I have seen some customers tweak the generic SQL Control Append and build an Oracle Control Append that does direct path inserts - this should really exist but doesn't.