Data Warehousing - Partitions

A partition cut out the storage of a table in several part.

You can have two types of partition :

• horizontal (related to a cutting by row)
• vertical (related to a cutting by column)

For more information, see this page : Database - Row Store of Column Store ?

• OWB - Partition Exchange Loading

Taking advantage of range and composite range-range partitioning for fact tables reduces index and statistics maintenance time during ETL processes as well as improves query performance. Because the majority of inserts and updates impact the last partition(s), you only need to disable local indexes on a few impacted partitions, and then rebuild disabled indexes after the load and compute statistics on updated partitions only. Query should also render results faster, since the optimizer builds more efficient execution plans using partitioning elimination logic.

Large fact tables, with more than 20 million rows, can be suitable for partitioning. To build an optimal partitioned table with reasonable data distribution, you can consider partitioning by month, quarter, year, and so on.

The recommended partitioning range for most implementations is a month; however, you may want to consider implementing quarterly or yearly partitioning ranges.

The majority of incremental ETL volume data is made up of new records, which would be stored in one of the two latest partitions. Depending on the range granularity you chose, it is recommended that you rebuild local indexes for the most impacted of the latest partitions, as described below:

• Monthly range. It is recommended that you maintain the two latest partitions, that is, the current and previous partition.
• Quarterly range. It is only necessary to maintain the current partition.
• Yearly range. It is recommended that you maintain the current partition.