delete from command-- increases table size?

[Mr. Bigglesworth]

Just wondering on this one, no big deal really.

I've copied a table over from one db to another, then add it via "addtable -l". At this point, the size of the .tbl file is 112k.

When I do a "delete from" command on that table, it then expands in size to 175k. Sounds counterintuitive to me, and I was just wondering if you could shed some light on that, and if I should be concerned if I need to delete a lot of records from a table-- that extra table size won't actually hurt search times much, will it?

[Kai]

Deleted blocks are saved for re-use in a manner that increases update/insert speed for heavily modified tables, as well as using space as efficiently as possible. The tradeoff is a slight increase in file size when deleting many rows without re-inserts. Search speed is not generally affected, assuming you have the proper indexes on the table, because the indexes point directly to the rows. Linear searches, however, may be slower, because of all the deleted blocks that must be skipped. For a ~100KB table there's probably not much difference.

If you are deleting a large number (over 50%) of the rows of a table, and want to regain the empty space, you can copy the table to another (either in SQL or with copydbf). Drop the indexes first, and re-make them after. This really only makes sense if you're not going to be doing much further modification to the table (otherwise those deleted blocks will be re-used) and the table is large (several hundred MB or more).