Subject Re: Database design question
Author Adam
--- In, ian <ian@...> wrote:
> Hi,
> 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.
> With Firebird it is not possible to specify in which file(s) a
> particular table or index is placed, but is there any change in
> performance, either better or worse, using multiple files?

No, the split file use is largely irrelevant for Firebird now, it was
to work around the OS 2GB maximum file size limitations (FAT32 etc),
but these limits are now significantly large enough.

I am not sure if Firebird is capable of placing the different drives
on different drives.

Firebird works in terms of pages, chunks of data (usually 4KB). These
pages contain one type of thing, either data, indices, generators,
blobs, and a couple of other things.

Which file the page is read or written to is of little significance to
the engine.

> 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?

It depends on what your database does. If you never update or delete
things, then I suppose most the new records will be in the final file.
If you do update or delete, then Firebird will (eventually) reuse that
space providing your transactions are reasonably well managed and
garbage collection runs fine, so such assumptions may not be safe.

In any case, I doubt it would help performance of sweeps.

> 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?

No, its not that clever.