Physical Design   «Prev  Next»

Lesson 10Vertical Partitioning
ObjectiveDescribe the characteristics of Vertical Partitioning

Database Vertical Partitioning

Horizontal partitioning redistributes the records in a table into two or more tables in order to reduce the size of the tables used in the most common searches and joins. It is also possible to partition a table into two or more tables that contain every row but not all of the columns in the original table. This process, called vertical partitioning and lets you store the most commonly used columns from a table in a smaller number of blocks on the computer's hard drive.

Vertical Partitioning Theory

Partitioning in database design is the process of assigning a logical object (relation) from the logical schema of the database to several physical objects (files) in a stored database. Vertical partitioning subdivides attributes into groups and assigns each group to a physical object. Horizontal partitioning subdivides object instances tuples[1] into groups, all having the same attributes of the original object. We refer to the physical objects that are a result of vertical or horizontal partitioning as horizontal or vertical fragments.
We use the term vertical partitioning somewhat loosely, in the sense that we allow fragments to overlap, so that partitions are not necessarily disjoint.

Vertical partitioning is used during the design of a database to improve the performance of transactions. Fragments consist of smaller records, and therefore fewer pages in secondary memory are accessed to process a transaction. When allocating data to a memory hierarchy, vertical partitioning is used to store the attributes that are most heavily accessed in the fastest memory. In the design of a multiple-site distributed database, fragments are allocated, and possibly replicated, at the various sites.
Vertical fragments are ultimately stored in the database by using some physical file structure.
In order to obtain improved performance, fragments must be "closely matched" to the requirements of the transactions. The ideal case occurs when each transaction "matches" a fragment, because the transaction has to access that fragment only. If certain sets of attributes are always processed together by transactions, the design process is trivial. In real-life applications one rarely comes across such trivial examples. Hence, for objects with tens of attributes, we need to develop a systematic approach to vertical partitioning.

Consider the records in the following table.
CD Table will be partitioned in 2 tables.

If you discovered that Stories on CD users needed to know the values for a CD's Title and DistID much more often than the values for the CD's Price and AgeGroup, you could divide the table into two tables linked by the original table's primary key field.

CD Table 1: Title and DistID; CD Table 2: Price and AgeGroup

The next lesson discusses the trade-offs inherent to vertical partitioning.
[1]tuple: In the context of relational database design, tuples are another word for the rows of a database table.