Subject Re: database size remains constant after deletion of records
Author Adam
--- In, Deepak Gupta
<deepak_cs_mca_2006@...> wrote:
> Hi Adam,
> I got you point.
> Could you tell me what is the use of sweep and what exactly the
Garbage Collector does with the database?

In a MGA database, each transaction can have a truly isolated view of
the data. If someone else inserts a new record, I don't see it (unless
I choose read committed). If someone else deletes a record, I still
see it. If someone else changes a record, I still see the old version.

Firebird achieves this by writing the transaction number next to the
record version. In fact, delete is just a special type of record
version which tells your transaction that the record is deleted.
Depending on your transaction number, and the committed state of the
transaction that made the delete, Firebird can work out whether you
'see' the record or not.

A record version is considered interesting if there is a transaction
that can potentially still see that record version. Eventually, all
the transactions that could have been interested will be complete
(committed or rolled back). Only at that stage is it safe to reuse the
space occupied by that record version. That is what garbage collection
is about.

Of course if you leave transactions active for extremely long periods
of time, you keep the deleted records interesting and the space they
occupy can not yet be reused. That is why you will see the advice on
this list to keep transactions as brief as possible, so you don't
prevent the garbage collection from doing its job.

There is another experts article on IBPhoenix which describes how
garbage collection, OAT, OIT, sweep, etc fits together if you want to
find out more, but sweeping doesn't mean compressing or packing.

Don't forget that it is hard to find a new hard disk with under 100GB,
so the days of scratching around for the extra 200MB on the database
server are gone.