At 08:38 AM 11/06/2003 +0100, you wrote:

> > Instead of deleting the records, how about recycling old records with an
> > Update. Only insert new records when there is no out-dated records
> > available.
>
>I can see where you are coming from, and if you were using
>dBase or some other fixed record database it would be right.
>
>However since records in Firebird are dynamically
>positioned, and creating a new record by updating an old one
>would mean that the old data is copied to a new record, and
>then the data changed, and the changes 'transactioned', it
>has to be faster to delete the 'old' record and then just
>create a new one - no copying information in the engine.
>
>It's one of those problems where understanding exactly how
>the engine works would be nice, but it's not necessary if
>you just remember that any update COPIES the existing record
>to a 'new' one, makes the change, and then decides which
>copy to keep. Delete will mark a 'space' as available, so
>that 'insert' can use it directly without the extra copying
>- ignoring garbage collection ;).

Errm, not quite, Lester. Delete actually leaves a stub; and stubs are not
removed except by gbak with garbage collection on. Also, if I understood
Ann's recent writeup in Architect re index cleanup, the indexes of stubs
may persist until the next gbak, too. So, in terms of making space
available, deleting + inserting has no advantage over updating and leaving
a back version.

Since background GC frees up space occupied by obsolete back versions, the
updating approach appears to offer an economy of scale unless you are
gbak-ing regularly.

heLen