Physical Design   «Prev  Next»

Lesson 8 Horizontal Partitioning
ObjectiveDescribe the characteristics of Horizontal Partitioning

Horizontal Partitioning Characteristics

You should consider partitioning a table horizontally when the table grows so large that searches and joins using the table become unacceptably slow. For example, your database might contain a table listing every order placed by Stories on CD.

Horizontal Splitting

One technique for reducing the size of a table accessed by a query is to split it into two or more tables with the same columns and to allocate the rows to different tables according to some criteria. In effect we are defining and implementing subtypes. For example, although it might make sense to include historical data in the same table as the corresponding current data, it is likely that different queries access current and historical data. Placing current and historical data in different tables with the same structure will certainly improve the performance of queries on current data. You may prefer to include a copy of the current data in the historical data table to enable queries on all data to be written without the UNION operator. This is duplication rather than splitting.

ORDER table consisting fo 1) OrderNo 2) OrderDate 3) CDNo 4) OrderCost 5) Received

Partition by Date

Since Stories on CD is a successful, growing business, the table will most likely grow to an unmanageable size quite quickly.
Even though all of the order data is important, you may find that Stories on CD employees most frequently access records for orders placed over the previous three months. Rather than store all orders in a single table, you could choose to partition the table horizontally so that only those orders from a given three-month period (quarter) are stored in a single table.
Partitioning the Orders table in this manner would reduce the size of the table the RDBMS would need to search while still meeting most of the needs of the users .The next lesson discusses the disadvantages of horizontal partitioning.
When placing a table in first normal form, one ends up with one large table with one primary key. Likewise with graphical normalization, we end up with on large graph/diagram after bringing the known attributes to first normal form.