Creating Horizontal Partitions

Horizontal partitioning consists in segmenting a table into multiple tables each containing a subset of rows and the same columns as the partitioned table in order to optimize data retrieval. You can use any column, including primary keys, as partitioning criteria.

In this example, the table Annual_Sales contains the following columns:

This table may contain a very large amount of data. You could optimize data retrieval by creating horizontal partitions by year. The result is as follows:

Horizontal partitioning has the following pros and cons:

Pros	Cons
Improve the query response time	Requires additional joins and unions to retrieve data from multiple tables
Accelerate incremental data backup and recovery	Requires more intelligent queries to determine which table contains the requested data
Decrease time required to load into indexed tables	Requires additional metadata to describe the partitioned table

You can partition tables horizontally using the Horizontal Partitioning Wizard.

1. Select Tools > Denormalization > Horizontal Partitioning, or right-click a table in the diagram and select Horizontal Partitioning, in order to open the Horizontal Partitioning Wizard:
   
   
   
2. Select the table to partition and select the check box if you want to keep the original table after partitioning. Then click Next to go to the Partition Definition page.
3. The Partition Definition page allows you to create as many partitions as you need with the Insert and Add a row tools. The name of each partition must be unique in the model. A table will be created for each partition you specify, and will take the name of the relevant partition. Then click Next to go to the Discriminant Column Selection page.
4. The Discriminant Column Selection page allows you to specify the columns that will be used as partition criteria using the Add Columns tool. These columns will not be included in the partitions. Then click Next to go to the Partitioning Information page.
5. The Partitioning Information page allows you to specify a name and code for the transformation object that will be created together with the partitions. Then click Finish.

   The table is partitioned, a horizontal partitioning object is created, and all references to the original table are created on each partition table.