Databases Reference
In-Depth Information
is accessed much more frequently than the rest of the records in the table.
Another is to manage the different groups of records separately for security or
backup and recovery purposes. For example, you might need to access the
records for sales managers in the CUSTOMER EMPLOYEE table more fre-
quently than the records of other customer employees. Separating out the more
frequently accessed group of records means that they can be stored near each
other on the disk, which will speed up their retrieval. The records can also be
stored on an otherwise infrequently used disk, so that the applications that use
them don't have to compete with other applications that need data on the same
disk. The downside of horizontal partitioning is that a search of the entire table
or the retrieval of records from more than one partition can be more complex
and slower.
In vertical partitioning a table is subdivided by columns, producing the same
advantages as horizontal partitioning. In this case, the separate groups, each
made up of different columns of a table, are created because different users or
applications require different columns. For example, it might be beneficial to
split up the columns of the SALESPERSON table so that the Salesperson Name
and Year of Hire columns are stored separately from the others. But note that in
creating these vertical partitions, each partition must have a copy of the primary
key, Salesperson Number in this example. In all but one of the tables it is also
used as the foreign key, relating all of the vertically partitioned tables as a set. The
major disadvantage is one you've already seen. A query that involves the retrieval
of complete records, that is, data that is in more than one vertical partition,
requires that the vertical partitions be joined to reunite the different parts of the
original records.
In some situations, vertical partitioning may be physically required by the
DBMS. For example, when using SQL Server a table's row size is limited to
approximately 8 KB. This is a physical limit that cannot be exceeded. If you have
too many attributes, or the attributes are too large to fit in a single physical row,
you must vertically partition the table into multiple tables.
SELF-CHECK
•
List and describe key data integrity requirements.
•
Explain how entity integrity can be enforced if an entity does not
have a uniquely-valued attribute.
•
Describe changes you might make to table design to optimize
performance.
