Now that you know how many records can fit in each block optimally, you can review how to distribute the tables across storage areas. Some of the more common reasons to split information across areas include:
Controlled distribution of I/O across areas
Application segmentation
Improve performance of offline utilities
Knowing how a table is populated and accessed, helps decide whether or not to break a table out to its own area. In tables where records are added to a table in primary index order, most of the accesses to these records are done in sequential order via the primary index, and there might be a performance benefit in isolating the table. If this is a large table, the performance benefit gained through isolation can be significant. There are two reasons for the performance improvement:
One database read will extract multiple records from the database, and the other records that are retrieved are likely to be used. This improves your buffer hit percentage.
Many disk drive systems have a feature that reads ahead and places items in memory that it believes are likely to be read. Sequential reads take advantage of this feature.
Finally, databases can contain different types of data in terms of performance requirements. Some data, such as inventory records, is accessed frequently, while other data, such as comments, is stored and read infrequently. By using storage areas you can place frequently accessed data on a "fast disk." However, this approach does require knowledge of the application.
Controlled distribution of I/O across areas