I have an requirement , I need to write an ETL tool using Oracle SQL and PLSQL for applying transformation and then loading into the target tables. The data is already loaded in the staging tables , I need to come with the performance oriented code to do the transformation. I have around 15 lakh records in my staging table.
Iam planning two approach for this ,
1. Using normal SQL statements to apply the transformation and use temporary tables to store the data and then load into the target tables.
2. Use PLSQL collections to process 1000 records at a time and use bulk insert into temporary tables and then load them into target tables.
In both the cases i need to mutilple tables to fetch data and then apply the transformation. The transformation can be applied by looking into some lookup tables or direct formating of the data.
I want to know which method is best for this requirement.
Any help is really appreciated.