Why partitioning:

1. Enhances the performance, manageability, and availability.
2. Partitioning allows tables, indexes, and index-organized tables to be subdivided into smaller pieces.

When to partition a table:

1. Tables larger than 2gb size.
2. Historical data
3. Contents must be stored in different storage
4. Avoid index maintenance when data is removed
5. Index maintenance on parts of data like rebuilding

Benefits of partitioning:

1. For performance: partition pruning, partition wise joins
2. For manageability: backup of single partition
3. For availability: different storage device

How to partition:

• Single level partitioning:
  • Range: maps data to partitions based on ranges of values
  • List: a region column as the partitioning key
  • Hash: data is not historical or has no obvious partitioning key.

A MAXVALUE literal can be defined for the highest partition. MAXVALUE represents a virtual infinite value that sorts higher than any other possible value for the partitioning key, including the NULL value.

 

 

• Composite level partitioning: (primary partition-sub partition)
  • range-hash, range-list, list-range, list-hash, hash-range, hash-list

• Interval Partitioning: Interval partitioning is an extension of range partitioning which instructs the database to automatically create partitions of a specified interval when data inserted into the table exceeds all of the existing range partitions. You must specify at least one range partition. The range partitioning key value determines the high value of the range partitions, which is called the transition point, and the database creates interval partitions for data with values that are beyond that transition point.

 

• Reference Partitioning: Reference partitioning enables the partitioning of two tables that are related to one another by referential constraints. The partitioning key is resolved through an existing parent-child relationship, enforced by enabled and active primary key and foreign key constraints.The benefit of this extension is that tables with a parent-child relationship can be logically equipartitioned by inheriting the partitioning key from the parent table without duplicating the key columns.

Before Reference Partitioning:

After Reference Partitioning:

• Virtual Column-Based Partitioning: Virtual columns enable the partitioning key to be defined by an expression, using one or more existing columns of a table. The expression is stored as metadata only.Oracle Partitioning has been enhanced to enable a partitioning strategy to be defined on virtual columns. For example, a 10-digit account ID can include account branch information as the leading three digits. With the extension of virtual column based partitioning, an ACCOUNTS table containing an ACCOUNT_ID column can be extended with a virtual (derived) column ACCOUNT_BRANCH. ACCOUNT_BRANCH is derived from the first three digits of the ACCOUNT_ID column, which becomes the partitioning key for this table.

 

• Indexing on Partitioned Tables: Indexes on partitioned tables can either be non-partitioned or partitioned. As with partitioned tables, partitioned indexes improve manageability, availability, performance, and scalability. They can either be partitioned independently (global indexes) or automatically linked to a table’s partitioning method (local indexes). In general, you should use global indexes for OLTP applications and local indexes for data warehousing or decision support systems (DSS) applications.

Reference: Oracle Database VLDB and Partitioning Guide