How to reformat the highwatermark of a table after a delete?

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

This will remove all the content of the table and will put the ‘highwatermark’ back to 0.

The advantage of this option is that is will will work a bit faster then a normal deletion.
But there is also one disadvantage. If you want to use this, you will have to disable all the foreign keys for this table before starting the truncate.

So doing this in a live database is not really the best thing to do I believe 🙂

The other option is just use the delete as you used to do, but after the deletion of the records use : ALTER TABLE

You can even use the cascade option for this shrink, this will shrink all the highwatermarks of the depending objects.
This option is only available from oracle 10g.

Thanks Erwin and Hans for helping me with this!


2 thoughts on “How to reformat the highwatermark of a table after a delete?

  1. 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

  2. Jens,You are absolutely right, I have changed the blog post.Thank you for pointing me on this misunderstanding!Frederik

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

About Tuur Hendrickx