About Splitting Partitions and Subpartitions

The SPLIT PARTITION clause of the ALTER TABLE or ALTER INDEX statement is used to redistribute the contents of a partition into two new partitions. Consider doing this when a partition becomes too large and causes backup, recovery, or maintenance operations to take a long time to complete or it is felt that there is simply too much data in the partition. You can also use the SPLIT PARTITION clause to redistribute the I/O load. This clause cannot be used for hash partitions or subpartitions.

If the partition you are splitting contains any data, then indexes may be marked UNUSABLE as explained in the following table:

You cannot split partitions or subpartitions in a reference-partitioned table except for the parent table. When you split partitions or subpartitions in the parent table then the split is cascaded to all descendant tables. However, you can use the DEPENDENT TABLES clause to set specific properties for dependent tables when you issue the SPLIT statement on the master table to split partitions or subpartitions.

This section contains the following topics:

• Splitting a Partition of a Range-Partitioned Table

• Splitting a Partition of a List-Partitioned Table

• Splitting a Partition of an Interval-Partitioned Table

• Splitting a *-Hash Partition

• Splitting Partitions in a *-List Partitioned Table

• Splitting a *-Range Partition

• Splitting Index Partitions

• Splitting into Multiple Partitions

• Fast SPLIT PARTITION and SPLIT SUBPARTITION Operations