I have multiple staging tables containing data that needs to be moved to production tables. The process of moving this data is done within a transaction to make it possible to rollback everything in case the process fails. The most log consuming operations are:
- Deleting old data from production tables
- Inserting new data into production tables
The query used to delete the old data is:
DELETE FROM PROD_TABLE WHERE CONDITION
A simple truncate is not possible since not all the data can be deleted.
The data to be deleted and the data to be moved is about 200 Million records.
Is there anyway to improve the process to reduce the log consomption.