Partitioning in Data Warehouses

Partitioned tables and indexes facilitate administrative operations by enabling these
operations to work on subsets of data. For example, you can add a new partition,
organize an existing partition, or drop a partition with minimal to zero interruption to a read-only application.

Using the partitioning methods described in this section can help you tune SQL
statements to avoid unnecessary index and table scans (using partition pruning). You
can also improve the performance of massive join operations when large amounts of
data (for example, several million rows) are joined together by using partition-wise
joins. Finally, partitioning data greatly improves manageability of very large databases and dramatically reduces the time required for administrative tasks such as backup and restore.

Granularity in a partitioning scheme can be easily changed by splitting or merging
partitions. Thus, if a table's data is skewed to fill some partitions more than others, the ones that contain more data can be split to achieve a more even distribution.

Partitioning also allows one to swap partitions with a table. By being able to easily
add, remove, or swap a large amount of data quickly, swapping can be used to keep a
large amount of data that is being loaded inaccessible until loading is completed, or
can be used as a way to stage data between different phases of use. Some examples are
current day's transactions or online archives.

Posted in: Partitioning