Mark Kirkwood wrote:
> Andrew Dunstan wrote:
>
>>
>> (I should have thought a single file DB would be MORE liable to
>> fragmentation, BTW).
>>
>
> The likes of Oracle/SQLServer preallocate the files(s) at database
> creation time, so there will be little or no initial fragmentation.
>
... To clarify - preallocated with free space in the files :-) ...
A related point comes to mind, consider the single "tablespace" file for
example: It may have no fragmentation from a *file* perspective, but
supposing you have several busy relations being inserted or updated then
pages (or groups of pages) for these could be "fragmented" throughout
the tablespace file. This can reduce scan performance, since seeks are
now involved to "jump over" the page chunks for relations you are not
interested in.
Some Oracle DBA's I knew used to regularly export|rebuild|import their
databases precisely to "defragment" their relation|file page distribution.
I'm not trying to say that we don't have an issue, just pointing out
that preallocated files are not necessarily a complete or ideal solution.
Cheers
Mark