Christian Kaufmann wrote:

>
> OK. But if I understand correctly, even an update of a single record,
> where I change only the field SWIMRESULTID forces Firebird to add a
> new copy of it and then, a new page will be necessary?

Data is stored on data pages and index entries are stored on b-tree
pages, so the number and types of indexes don't affect data storage.

If you use the -use_all_space switch, the database will put as many
records as can possibly fit on each page. When you modify a record,
Firebird first checks to see if the old version will fit on the
same page with the new version. If not, then the old version will
be put on a previously allocated data page for that table that does
have space. First choice goes to dirty pages in the cache, second
to other data pages in the cache, third to pages on disk.

If there is no previously allocated data page for that table
with space, Firebird will take a free page in the file and
allocate it to the table. If there are no free pages in the
file, Firebird will extend the file and allocate the new page to
the table.

When the back version is stored on a page, that page is available
for new records or back versions. Eventually, the back version
will become unnecessary and it will be removed, leaving space on
that page for a back version of one of the newer records.

Until InterBase 3, we didn't reserve space for modifications. The
first set of updates and deletes after the data was loaded tended
to create a lot of I/O as back versions required new pages but
eventually removing old versions and deleted records would create
space and things got faster. The reserved space was added to
reduce that "settling in" cost, but given the size of your
records, the algorithm produces too much free space and you
do excessive I/O because pages are only 60% full.

>
> Is there a rule, how records are grouped on a page when doing a
> restore?

A restore puts the records back in the order it found them. All
the records for a table will be stored on contiguous pages, give
or take any system internal page types (e.g. TIP) that need to
be created.

>
> Originally it's more or less random, how the records were added. But
> when adding new records, most of them will be added with a new value
> of RANKINGCLUBID. So I'm just interested, if for example the primary
> key makes Firebird to group the records by RANKINGCLUBID and like
> this, pages with old records stay untouched?

Firebird doesn't cluster records based on values. Records are stored
on pages in the order they were inserted.


Regards,


Ann