Databases Reference
In-Depth Information
Figure 5-11
SALESPERSON
Salesperson
Number
Salesperson
Name
Commission
Percentage
Year
of Hire
Office
Number
Total Annual
Customer Purchases
CUSTOMER
Customer
Number
Customer
Name
Salesperson
Number
Annual
Purchases
HQ City
Modified CUSTOMER and SALESPERSON tables.
partitioning
is partitioning by column, storing different columns in different
tables. The tables can be stored in different tables on the same database with the
best performance improvement coming when the tables are stored on different
physical hard disks. You can also store the data in different tables on different
servers in a distributed processing environment.
In horizontal partitioning, the rows of a table are divided into groups, writ-
ten to different tables, and stored on different areas of a disk or (usually) on
different disks. This may be done for several reasons. The obvious one is to
improve data retrieval performance when, for example, one group of records
FOR EXAMPLE
Splitting Tables
Consider a situation where a company has offices in different geographic loca-
tions. Each location keeps an inventory specific to its warehouse's needs. You
keep a master inventory list on a database server in the home office in San
Diego, California. You also have a database server at each of the warehouse
locations. At the warehouses, you want to minimize data storage require-
ments and optimize performance when performing inventory lookups.
This is a situation where you would split a table across multiple loca-
tions. The database server would store only those records appropriate to that
warehouse, keeping the size of the INVENTORY table to a minimum. Reduc-
ing the table size also helps improve performance when retrieving data. You
can have the database servers in each of the warehouses send updates to the
server in San Diego to keep the master inventory file up-to-date.
