Example On How to Quickly Perform a SPLIT PARTITION Using FAST SPLIT (Doc ID 1921259.1)

Last updated on MARCH 10, 2023

Applies to:

Goal

To split an existing partition of a partitioned table, the command to use is:

This will split the partition into two new partitions called newpartition1 and newpartition2 with the split occurring at the partition key value. All rows with a partition key value less than value will be moved to newpartition1, and the remaining rows of the partition will be moved to newpartition2.

Note that all rows will be moved from the original partition to one of the new partitions. This means that:

- The operation may require a long time to complete.
- Table / partition locks may be held for a long time.
- Global indexes will be invalidated and need to be rebuilt. 

It may not be possible to keep such a table locked for an extended period of time or to rebuild many global indexes.

It is often possible to avoid long running split operations and index rebuilding with the method known as fast split.

Fast Split can be used with some knowledge of how the database decides to make use of this method and plan the split accordingly.

Solution

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.