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:

Table Type Index Behavior

Regular (Heap)

Unless you specify UPDATE INDEXES as part of the ALTER TABLE statement:

  • The database marks UNUSABLE the new partitions (there are two) in each local index.

  • Any global indexes, or all partitions of partitioned global indexes, are marked UNUSABLE and must be rebuilt.


  • The database marks UNUSABLE the new partitions (there are two) in each local index.

  • All global indexes remain usable.

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: