Chapter 10 Denormalizing a PDM

Horizontal partitioning

Horizontal partitioning consists in segmenting a table into multiple tables each containing a subset of rows and the same columns as the partitioned table.

Horizontal partitioning optimizes data retrieval by dividing rows of a table.

Example

The table Annual_Sales contains the following columns:

This table is likely to contain a very large amount of data. You can use the Horizontal Partitioning Wizard to optimize data retrieval by creating partitions corresponding to sales per year. You can use any column, even primary keys, as partitioning criteria.

The result is the following:

Pros and cons

Horizontal partitioning allows you to divide a large table into smaller tables in order to:

Improve the query response time
Accelerate incremental data backup and recovery
Decrease time required to load into indexed tables

However, horizontal partitioning also implies to:

Create joins and unions to retrieve related data from multiple tables
Create more intelligent queries to determine which table contains the requested data
Use additional metadata to describe the partitioned table