I have some tables with millions of rows (up to 6MLN) and I need to execute a delete to reduce the amount of rows. I created a procedure with a loop that delete 10k record each execution.
What I did see is that after some executions the table starts to respond very slow and the delition no longer respond, for example:
- First itaration, 30k records to delete, time 35 seconds
- Second iteration, 126k records to delete, time 3.4 mins
- Third iteration, 350k records to delete, time 24 mins
Every iteration i place a commit every 10k record. So first iteration need to delete 30k records and commit every 10k records.
I see that every time i execute the procedure the table grow (and does not decrease, what i was expecting...)
I'm trying to find a good solution before this post, with no luck.
I cannot delete and re-create the table with less records, so i need to find a way to lower down the records on the table.
How i can do to delete all this records maintaining the performance? Free space, "well built indexes" etc
I can give you more details if needed