I have task for Purging the data from database. This data is from Audit table, hence it does not have any relationship with the other tables. There are around 40 audit tables and it has timestamp. I want to derive best strategy to purge the data. I have idea as below, please let me know your opinion.
1. I will write a Store procedure which will delete data from all 40 tables which are more than 1 months old.
2. I will write a java program to call SP and inturn Java program will be called by scheduler.
3. The Schedule will trigger at 15 of each month.
What is your opinion on the same, is my steps can be improved in tuns of performance.