ian wrote:

>
> I have a question on database design and the use of multiple files. In
> both Oracle and MS SQLServer, I always create a minimum of three data
> files: system stuff, data tables and indexes. If the system has more
> than one hard disk, then the index and data files will be placed on
> separate disks, for improved performance.

Firebird databases are essentially one file that can be split
into several physical files - an artifact of days when disks
were typically smaller than databases. There is no placement
control. That's not because we're lazy or ignorant, but because
Firebird handles disk access somewhat differently from Oracle
and SQLServer (and Sybase, and Ingres, and MySQL and ...)

In most database systems, when you ask for a set of records
based on an index key, the system first reads the index and
identifies the first qualifying entry, then reads the corresponding
record, then goes back to the index and finds the next qualifying
record, etc. Unless you separate indexes and data on different
disks, disk heads are jumping all over.

Firebird reads the index, finds the first qualifying entry and
marks the record number of that entry in a sparse bitmap. It
continues through the qualifying entries without looking at data.
The list of qualifying records is sorted by the storage order of
the records. When the list is complete, Firebird reads each
record in storage order - the most efficient physical order.
This mechanism works on single disk systems, multi-disk systems,
and RAID arrays without tuning.

System data isn't so interesting as it is read only on startup
and cached in memory until changed - relatively rare.

>
> ... is there any change in
> performance, either better or worse, using multiple files?

Probably worse, in general, unless you run out of space on a
single disk.

>
> Assuming that files are added sequentially and depending on database
> usage, it is likely that most transactions would be in the last file, so
> could this speed up sweeping?

Really doesn't matter much as all pages must be validated to catch
the removal of very old records.

>
> In the case of file corruption, is Firebird clever enough to recognise
> that only one file is a problem, or does the whole database need to be
> replaced?

The whole database needs to be replaced because of relationships
between data in the first part of the database and data in the
last. For example, there's a table called RDB$PAGES that keeps
track of interesting pages like transaction inventory pages and pointer
pages. When a new data page is created, it goes on a pointer page for
the table. When a new pointer page is created, a new record is stored
in RDB$PAGES. Since RDB$PAGES is an old, reasonably small table, it's
often found in the first part of the database - and certainly its
pointer pages are in the first part of the database. So adding a new
data page at the end of the database can affect something in the first
few hundred pages.


Regards,


Ann