THE PHYSICAL DESIGN PROCESS - Database Design and Development: An Essential Guide for IT Professionals - page 373

Fragmentation
When some tables in your database system are huge, with many attributes
and a large number of rows, it is not easy to manage and administer their
storage requirements. Huge tables need special considerations for improving
data access. You can manage a huge table if you partition it in the best possible
manner.
In some cases, part of the data in a huge table may be used less frequently than
the rest of the data. Then you may want to partition the more frequently used data
and store it on fast storage. In other cases, some parts of a huge table may be used
primarily by one user group and the rest used by other user groups. In such cases,
you may partition the table by user groups.
In the relational data model, a table may be partitioned and stored in multiple
files in the following ways.
Horizontal Partitioning Split the table into groups of rows. This type of parti-
tioning is appropriate for separating the data for user groups in geographically
dispersed areas. Horizontal partitioning is also applicable for separating active data
from old data.
Vertical Partitioning Split the table into groups of columns. This type of parti-
tioning is appropriate if some columns are used more frequently than others. Ver-
tical partitioning is also applicable if certain sets of columns are used by different
user groups.
Figure 12-20 indicates how a CUSTOMER table may be partitioned horizontally
or vertically.
Memory Buffer Management
The buffer manager is the software component responsible for bringing blocks of
data from disk to main memory as and when they are needed. Main memory is par-
titioned into collections of blocks called buffer pools. Buffer pool slots hold slots
for blocks to be transferred between disk and main memory. The buffer pool slots
must be managed efficiently to influence data access performance. Buffer pool
slots must be made available by clearing the data that are no longer required
for processing. The buffer manager must be able to keep track of buffer usage
properly so as to be able move the necessary volumes of data between disk and
main memory efficiently.
Proper buffer management improves data access performance. You may set the
DBMS parameters to accomplish this objective. You may improve performance in
the following ways:
• Allocating the right number of memory buffers so that only the needed data
are kept in the buffers and the needed data are retained in the buffers for the
right length of time.
• Determining the right buffer sizes so that the buffers are of optimum sizes—
large enough to accommodate the needed data and small enough not to waste
buffer space.