What happens with my index when I do 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 index uses a highwatermark value and every time you add a record into the table the index will raise this value.
When you do a simple delete the value of this highwatermark will be kept, so I was right in suspecting the index to be the cause of this problem.

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 indexes 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 that is will work a bit faster then a normal deletion.
But there is also one disadvantage however. 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 SHRINK SPACE
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!