At 12:07 PM 28/02/2003 -0500, you wrote:

>Helen,
>
>I read on an Interbase forum that doing a select count(*) from <tablename>
>performs a GC on that table. Is this true in Firebird?
>
>If I don't do a back/restore, does Firebird reuse the empty data pages for
>new data? If yes, I suppose that's where the data fragmentation comes from...

No, not until the space has been released. As for "data fragmentation",
perhaps my "defrag" analogy gave the wrong idea. Since Firebird doesn't
store tables in any kind of contiguous structure, data fragmentation, per
se (from the point of view of the database management), doesn't
occur. Data are stored on "pages", i.e. blocks of memory. There might be
only one "row" stored on a page or, if the row structure is small enough,
there might be more than one. A row structure that won't fit on one page
will occupy multiple pages. The size of a page depends on what you have it
configured to (PAGE_SIZE), typically 4K or 8K; although Fb lets you have a
16K page size.

A page can be anywhere at all in the database file's on-disk structure.

So, in the rebuild of the database with a restore, the "defrag" of my
analogy is a freeing up of dead space, not the kind of data coalescence
that happens when you defrag a filesystem. In the case of structures where
multiple rows can fit economically onto a single page, page allocation
starts from scratch with pages being filled up to the fill capacity
(usually as near as possible to 80%, if the NOSPACE option isn't in
effect). This means the restored database will have fewer pages overall if
the old db had a lot of obsolete deletes.

Obsolete record versions are also stored on database pages. These pages
get cleared out by a restore as well.

>So, it look like I'm going to have to do a backup/restore periodically.

Yes, highly recommended for good database hygiene.

Remainder answered by Ded...

heLen