Hi

I've recently noticed that deleted records are still contained within tables and are not removed until it is emptied or optimized. E.g. One of my tables has had the query 'delete from table' but the file size us around 41mb and it's not until I run an optimize the table returns to around 14kb. Opening the table with a binary editor still shows the records so I was just wondering what the logic is and why they are still there etc...

Its the way a number of database systems work. The basic fact is that if the space was reclaimed immediately the record was deleted the entire table downwards from that record would need to be moved (think of optimising the table every time you delete a record). Its just not practicable to do it that way. Most of the systems I'm familiar with do it in the same way. A deleted flag is written against the record and its thereafter ignored.

The data is left in there because that way, even if you can't do it, it is possible that the data can be restored if a mistake was made. Where blobs are concerned bits might get overwritten before of after the rest of the record.

Final point is that both DBISAM and ElevateDB try to reuse deleted record space. I don't know what the efficiency is like but I suspect that with the exception of blobs is near to 100%



Roy Lambert [Team Elevate]

Hi Roy

Thats what I thought it was doing but wanted some clarification more than anything, so a couple of things i guess then. firstly how/can we restore the deleted records, when my application is produced then as apart of my upgrade process is it good then to optimize at that point to tidy up and make the tables more efficient? because i can definately see a big improvement when I run an optimization, im just wondering how often you would do that...

>Thats what I thought it was doing but wanted some clarification more than anything, so a couple of things i guess then. firstly how/can we restore the deleted records, when my application is produced then as apart of my upgrade process is it good then to optimize at that point to tidy up and make the tables more efficient? because i can definately see a big improvement when I run an optimization, im just wondering how often you would do that...


You can't undelete them but Tim can. The space reuse policy in DBISAM / ElevateDB could mean that part of the blob space has been used and it needs some careful checking / handling.

What optimisation basically does is write a new copy of the table in the sequence of the index selected, delete the old table and rename the new copy to the original name. So if for example, you have a contacts table that is generally accessed in surname order then as you add contacts you're going to get Zekinski next to Adams next to Gilbert etc. Optimizing would put them all in order so that when you read the table the various caching mechanisms are more likely to be effective thus speeding up things. It would also mean that deleted records are removed thus making the table smaller and again speeding things up.

How often you optimise depends on how long it takes and what the effects are. At one time I had a routine in place that ran over the weekend - backup, optimise tables, defrag disk.

I think doing it at upgrade makes sense - the apps not in usage then anyway. If you're altering table structure then optimising first could even end up speeding the whole process up.

What I tend to do these days is my apps all come with options to optimise, repair, backup tables with checkboxes to allow the tables to be selected and out the user in charge.

Roy Lambert [Team Elevate]

Thanks Roy it's all making more sense now. I figured I couldn't 'undelete' the records but Tim could lol. That's kool...

One last thing but, i have optimized a table and ended up with more records than I started with. Does that mean they were possibly corrupt or hidden in some way so now im seeing the table as I should have before?

My best guess is that one or more indices were corrupted. The data was physically there but if the index is wrong you just won't see it. If you get that effect much I'd suggest you have grounds to start worrying.

Roy Lambert [Team Elevate]