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
I think you are mixing things up a bit:The HighWaterMark (HWM) has not got to with the index, but is for the table itself. A full tablescan will read all blocks up to the HWM, whether there are rows in the blocks or not. You are correct in that the best way of clearing out is to use “alter table tablename shrink space”. The drawback is however that subsequent insert statements will have to reallocate these blocks. So you need to check the usage of the table before adding this costly operation to all of your delete procedures.Just my 2 cents..Jens
Jens,You are absolutely right, I have changed the blog post.Thank you for pointing me on this misunderstanding!Frederik