[转]Optimizing SPLIT PARTITION and SPLIT SUBPARTITION Operations

Optimizing SPLIT PARTITION and SPLIT SUBPARTITION Operations

 

 

 

Oracle Database implements a SPLIT PARTITION operation by creating two new
partitions and redistributing the rows from the partition being split into the two new
partitions. This is an expensive operation because it is necessary to scan all the rows of
the partition being split and then insert them one-by-one into the new partitions.
Further if you do not use the UPDATE INDEXES clause, both local and global indexes
also require rebuilding.
Sometimes after a split operation, one of the new partitions contains all of the rows
from the partition being split, while the other partition contains no rows. This is often
the case when splitting the first partition of a table. The database can detect such
situations and can optimize the split operation. This optimization results in a fast split
operation that behaves like an add partition operation.
Specifically, the database can optimize and speed up SPLIT PARTITION operations if
all of the following conditions are met:

 

 

■ One of the two resulting partitions must be empty.

 

 

 

 

 

■ The non-empty resulting partition must have storage characteristics identical to
those of the partition being split. Specifically:

 

 

 – If the partition being split is composite, then the storage characteristics of each
subpartition in the new non-empty resulting partition must be identical to
those of the subpartitions of the partition being split.

 

 

– If the partition being split contains a LOB column, then the storage
characteristics of each LOB (sub)partition in the new non-empty resulting
partition must be identical to those of the LOB (sub)partitions of the partition
being split.

 

– If a partition of an index-organized table with overflow is being split, then the
storage characteristics of each overflow (sub)partition in the new nonempty
resulting partition must be identical to those of the overflow (sub)partitions of
the partition being split.

– If a partition an index-organized table with mapping table is being split, then
the storage characteristics of each mapping table (sub)partition in the new
nonempty resulting partition must be identical to those of the mapping table
(sub)partitions of the partition being split.

 

If these conditions are met after the split, then all global indexes remain usable, even if
you did not specify the UPDATE INDEXES clause. Local index (sub)partitions
associated with both resulting partitions remain usable if they were usable before the
split. Local index (sub)partition(s) corresponding to the non-empty resulting partition
will be identical to the local index (sub)partition(s) of the partition that was split.

The same optimization holds for SPLIT SUBPARTITION operations.