When to Partition a Table and an Index

1. Global indexes can be unique. Local indexes can only be unique if the partitioning key is a part of the index key.

2. Note: All partitions of a partitioned object must reside in tablespaces of a single block size.

3. Any table can be partitioned into a million separate partitions except those tables containing columns with LONG or LONG RAW data types. You can, however, use tables containing columns with CLOB or BLOB data types.

4. Note:To reduce disk usage and memory usage (specifically, the buffer cache), you can store tables and partitions of a partitioned table in a compressed format inside the database. This often leads to a better scaleup for read-only operations. Table compression can also speed up query execution. There is, however, a slight cost in CPU overhead.

5. When to Partition a Table；
Here are some suggestions for when to partition a table:
Tables greater than 2 GB should always be considered as candidates for partitioning.
Tables containing historical data, in which new data is added into the newest partition. A typical example is a historical table where only the current month's data is updatable and the other 11 months are read only.
When the contents of a table need to be distributed across different types of storage devices.

6. When to Partition an Index：
Here are some suggestions for when to consider partitioning an index:
Avoid rebuilding the entire index when data is removed.
Perform maintenance on parts of the data without invalidating the entire index.
Reduce the impact of index skew caused by an index on a column with a monotonically increasing value.