PHYSICAL DATABASE DESIGN - Fundamentals of Database Management Systems - page 226

Without clustering, all the salesperson records are near each other on the disk, which
helps when retrieving subsets of them. With clustering, the salesperson records are
scattered over a much larger area on the disk because they're interspersed with all
of those customer records, slowing down the retrieval of subsets of just salesperson
records.

Splitting a Table into Multiple Tables
The three physical design techniques in this category arrange for particular parts of
a table, either groups of particular rows or groups of particular columns, to be stored
separately, on different areas of a disk or on different disks. In Chapter 12, when
we discuss distributed database, we will see that this concept can even be extended
to storing particular parts of a table in different cities.

Horizontal Partitioning In horizontal partitioning , the rows of a table are divided
into groups and the groups are stored separately, on different areas of a disk or on
different disks. This may be done for several reasons. One is to manage the different
groups of records separately for security or backup and recovery purposes. Another
is to improve data retrieval performance when, for example, one group of records
is accessed much more frequently than other records in the table. For example,
suppose that the records for sales managers in the CUSTOMER EMPLOYEE table
of Figure 5.14c must be accessed more frequently than the records of other customer
employees. Separating out the frequently accessed group of records, as shown in
Figure 8.23, means that they can be stored near each other in a concentrated space
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 excessively with other applications that need data on the same disk. The
downside of this horizontal partitioning is that it can make a search of the entire
table or the retrieval of records from more than one partition more complex and
slower.

Custome r Employee Employee

Numbe r Number Name Title

0933 30441 Levy Sales Manager

1525 33779 Baker Sales Manager

Custome r Employee Employee

Numbe r Number Name Title

0121 27498 Smith Co-Owner

0121 30441 Garcia Co-Owner

0933 25270 Chen VP Sales

0933 48285 Morton President

2198 27470 Smith President

2198 30441 Jones VP Sales

F I G U R E 8.23
Horizontal partitioning of the CUSTOMER
EMPLOYEE table

2198 33779 Garcia VP Personnel

2198 35268 Kaplan Senior Accountant