community.borland.com

Article #25469: What does a backup and restore, a sweep, a reindex and garbage collection do?

Problem:
It is not documented what these operations do in terms of maintaining a database, knowing what 
these operation do will help efficiently administer the database.

Solution:
A backup and restore will do the following:

- The pages in the database used by tables and indexes are allocated contiguously.
- Indexes are computed. And index tree is rebuilt.
- The index selectivity is recomputed for all indexes.
- The TIP (transaction information pages) pages containing old transactions and current transactions
are discarded and the transaction numbers are set to the beginning.
- All metadata versions are reset to 0 (some objects may start with a version of 1 0r 2). 
 InterBase allows only 255 versions of a record in a system table.
- All unused data pages are discarded.
- No back versions or deltas or records are present.
- No garbage index pages are present.
- All tasks that are performed by a sweep with a single connection.


A sweep does the following tasks:

- Brings forward the oldest interesting transaction (OIT) as much as possible.  If there is 
exclusive access the OIT number will be one less than the Next Transaction number.
- Touches every record in every table in the database that is accessible to the transaction 
that is being used.  This is similar to how a SELECT touches a record, triggering the garbage
collection mechanism.


Garbage collection does the following:
- Deletes all versions of a record older than the version of the OIT, and all delta records
 older than the version of the OIT.
- In InterBase v5.0.0 garbage collection of index pages also occurs.


A reindex (making an index inactive, then active) does the following:
- Rebuilds the index tree.
- The selectivity is recomputed for the index.


Last Modified: 26-OCT-00