For test purpose I had to delete and recreate a lot of records in a database.
So I just did a delete of all the records, every time I wanted to clean up my database.
Some of these tables have a few millions of records. So after a couple of times cleaning up and re-entering data into the database,
I had the impression that the database was getting slower and slower in showing me the results of my query.
I thought that this could have something to do with my indexes but I didn’t know how to fix this.
That’s why I contacted a few of my expert database colleagues and they learned me the following:
The table uses a highwatermark value and every time you add a record into the table the table will raise this value.
When you do a simple delete the value of this highwatermark will be kept, so I was wrong in suspecting the index to be the cause of this problem it was in fact the table itself who caused this.
So how do you fix this?
If you want to clean a lot of records in a database(in bulk) you have 2 possibilities to keep your Highwatermark clean.
First option, and the one that worked best in my case was: use TRUNCATE TABLE