Hi
I was wondering, with large file support available on almost any machine we encounter today, is there any benefit in having a lot of small fixed extents instead of one large one, besides that it enable you to work around storage constraints?
In other words, if you recreate a database and you are about to put 25 GB of an area in one folder, what is the benefit of making 25 x 1GB extents instead of a singe 25GB extent?
Regards
Simon
For the most part there is no benefit to using 25 extents of 1 gigabyte as compared to a single 25 gigabyte extent. On many UNIX and Linux systems with modern filesystems, there is no performance penalty for using large extents (this used to be known as the "triple indirection problem", which no longer occurs). Having fewer files is simpler for DBA people.
However:
I do not know of many people using extents larger than 10 or 15 gigabytes. There are a few with 50 gb.
Large files may sometimes be clumsy or inconvenient to work with simply due to their size. Copying them takes longer and you need adequate free space to make copies. This is mostly a minor point, though.
Like everything, filesystems have limits too. Be sure you know the limitations of whatever ones you use. Some filesystems only allow file sizes that are 4 gigabytes or less.
On some operating systems, with some filesystems, only one writer at a time can be updating a database block in a given file. If two processes want to write to different blocks in the same file, one will have to wait for the other even though there is no conflict. This means that using fewer extents provides a lower level of I/O concurrency.