Physical Design   «Prev  Next»

Lesson 9The downside to Horizontal Partitioning
ObjectiveDescribe the trade-offs that occur with Horizontal Partitioning

Disadvantages of Horizontal Partitioning

Splitting table records into two or more tables reduces the amount of data the RDBMS must work through in most searches and joins. The drawback of horizontally partitioning a table is that if the RDBMS must search every row in the table (to find, for example, the total of all orders to a given distributor), then the RDBMS must search each table for appropriate records. Because the RDBMS must
  1. open one table,
  2. find all appropriate records,
  3. close the table,
  4. find and open the next table,
  5. find all appropriate records,
and so on until every record has been searched, the search will take much longer than if the table was in one piece.
The only way to determine if horizontal partitioning[1] will improve database performance is to analyze usage patterns. If users rely more on one identifiable set of rows than another, it may be worthwhile to partition the table horizontally where the rows appear to allow faster access.

Characteristics of Partitioned Tables

A partitioned table stores different records in different locations, possibly on different hard disks or even different computers. If your typical queries normally divide the data along partition boundaries, the separate partitions can operate more or less independently. You may even be able to back up different partitions separately, improving performance. In a variation on partitioning, you use multiple databases to handle different parts of the data. For example, you might have different databases to handle customers in different states or time zones. Because the databases operate independently, they are smaller and faster, and you can back them up separately. You can extract data into a data warehouse to perform queries that involve more than one database.

[1]Horizontal partitioning: Distributing the tuples of a relation across a number of (smaller) relations.