Maintenance Operations on Partitions That Can Be Performed

This section discusses the maintenance operations that be performed on partitions, subpartitions, and index partitions.

Table 4-1 lists partition maintenance operations that can be performed on partitioned tables and composite partitioned tables
Table 4-2 lists subpartition maintenance operations that can be performed on composite partitioned tables
Table 4-3 lists maintenance operations that can be performed on index partitions, and indicates on which type of index (global or local) they can be performed

For each type of partitioning and subpartitioning in Table 4-1 and Table 4-2, the specific clause of the ALTER TABLE statement that is used to perform that maintenance operation is listed.

Note:

Partition maintenance operations on multiple partitions are not supported on tables with domain indexes.

Table 4-1 ALTER TABLE Maintenance Operations for Table Partitions

Maintenance Operation	RangeComposite Range-*	IntervalComposite Interval-*	Hash	ListComposite List-*	Reference
Adding Partitions, see About Adding Partitions and Subpartitions	`ADD PARTITION`, single and multiple partitions	N/A	`ADD PARTITION`	`ADD PARTITION`, single and multiple partitions	N/A
Coalescing Partitions, see About Coalescing Partitions and Subpartitions	N/A	N/A	`COALESCE PARTITION`	N/A	N/A¹
Dropping Partitions, see About Dropping Partitions and Subpartitions	`DROP PARTITION`, single and multiple partitions	`DROP PARTITION`, single and multiple partitions	N/A	`DROP PARTITION`, single and multiple partitions	N/A¹
Exchanging Partitions, see About Exchanging Partitions and Subpartitions	`EXCHANGE PARTITION`	`EXCHANGE PARTITION`	`EXCHANGE PARTITION`	`EXCHANGE PARTITION`	`EXCHANGE PARTITION`
Merging Partitions, see About Merging Partitions and Subpartitions	`MERGE PARTITIONS`, single and multiple partitions	`MERGE PARTITIONS`, single and multiple partitions	N/A	`MERGE PARTITIONS`, single and multiple partitions	N/A¹
About Modifying Default Attributes	`MODIFY DEFAULT ATTRIBUTES`	`MODIFY DEFAULT ATTRIBUTES`	`MODIFY DEFAULT ATTRIBUTES`	`MODIFY DEFAULT ATTRIBUTES`	`MODIFY DEFAULT ATTRIBUTES`
About Modifying Real Attributes of Partitions	`MODIFY PARTITION`	`MODIFY PARTITION`	`MODIFY PARTITION`	`MODIFY PARTITION`	`MODIFY PARTITION`
About Modifying List Partitions: Adding Values	N/A	N/A	N/A	`MODIFY PARTITION ADD VALUES`	N/A
About Modifying List Partitions: Dropping Values	N/A	N/A	N/A	`MODIFY PARTITION DROP VALUES`	N/A
Moving Partitions, see About Moving Partitions and Subpartitions	`MOVE SUBPARTITION`	`MOVE SUBPARTITION`	`MOVE PARTITION`	`MOVE SUBPARTITION`	`MOVE PARTITION`
Renaming Partitions, see About Renaming Partitions and Subpartitions	`RENAME PARTITION`	`RENAME PARTITION`	`RENAME PARTITION`	`RENAME PARTITION`	`RENAME PARTITION`
Splitting Partitions, see About Splitting Partitions and Subpartitions	`SPLIT PARTITION`, single and multiple partitions	`SPLIT PARTITION`, single and multiple partitions	N/A	`SPLIT PARTITION`, single and multiple partitions	N/A¹
Truncating Partitions, see About Truncating Partitions and Subpartitions	`TRUNCATE PARTITION`, single and multiple partitions	`TRUNCATE PARTITION`, single and multiple partitions	`TRUNCATE PARTITION`, single and multiple partitions	`TRUNCATE PARTITION`, single and multiple partitions	`TRUNCATE PARTITION`, single and multiple partitions

Table 4-2 ALTER TABLE Maintenance Operations for Table Subpartitions

Maintenance Operation	Composite *-Range	Composite *-Hash	Composite *-List
Adding Subpartitions, see About Adding Partitions and Subpartitions	`MODIFY PARTITION ADD SUBPARTITION`, single and multiple subpartitions	`MODIFY PARTITION ADD SUBPARTITION`	`MODIFY PARTITION ADD SUBPARTITION`, single and multiple subpartitions
Coalescing Subpartitions, see About Coalescing Partitions and Subpartitions	N/A	`MODIFY PARTITION COALESCE SUBPARTITION`	N/A
Dropping Subpartitions, see About Dropping Partitions and Subpartitions	`DROP SUBPARTITION`, single and multiple subpartitions	N/A	`DROP SUBPARTITION`, single and multiple subpartitions
Exchanging Subpartitions, see About Exchanging Partitions and Subpartitions	`EXCHANGE SUBPARTITION`	N/A	`EXCHANGE SUBPARTITION`
Merging Subpartitions, see About Merging Partitions and Subpartitions	`MERGE SUBPARTITIONS`, single and multiple subpartitions	N/A	`MERGE SUBPARTITIONS`, single and multiple subpartitions
About Modifying Default Attributes	`MODIFY DEFAULT ATTRIBUTES FOR PARTITION`	`MODIFY DEFAULT ATTRIBUTES FOR PARTITION`	`MODIFY DEFAULT ATTRIBUTES FOR PARTITION`
Modifying Real Attributes of Subpartitions, see About Modifying Real Attributes of Partitions	`MODIFY SUBPARTITION`	`MODIFY SUBPARTITION`	`MODIFY SUBPARTITION`
Modifying List Subpartitions, see About Modifying List Partitions: Adding Values	N/A	N/A	`MODIFY SUBPARTITION ADD VALUES`
Modifying List Subpartitions, see About Modifying List Partitions: Dropping Values	N/A	N/A	`MODIFY SUBPARTITION DROP VALUES`
Modifying a Subpartition Template	`SET SUBPARTITION TEMPLATE`	`SET SUBPARTITION TEMPLATE`	`SET SUBPARTITION TEMPLATE`
Moving Subpartitions, see About Moving Partitions and Subpartitions	`MOVE SUBPARTITION`	`MOVE SUBPARTITION`	`MOVE SUBPARTITION`
Renaming Subpartitions, see About Renaming Partitions and Subpartitions	`RENAME SUBPARTITION`	`RENAME SUBPARTITION`	`RENAME SUBPARTITION`
Splitting Subpartitions, see About Splitting Partitions and Subpartitions	`SPLIT SUBPARTITION`, single and multiple subpartitions	N/A	`SPLIT SUBPARTITION`, single and multiple subpartitions
Truncating Subpartitions, see About Truncating Partitions and Subpartitions	`TRUNCATE SUBPARTITION`, single and multiple subpartitions	`TRUNCATE SUBPARTITION`, single and multiple subpartitions	`TRUNCATE SUBPARTITION`, single and multiple subpartitions

Note:

The first time you use table compression to introduce a compressed partition into a partitioned table that has bitmap indexes and that currently contains only uncompressed partitions, you must do the following:

Either drop all existing bitmap indexes and bitmap index partitions, or mark them UNUSABLE.
Set the table compression attribute.
Rebuild the indexes.

These actions are independent of whether any partitions contain data and of the operation that introduces the compressed partition.

This does not apply to partitioned tables with B-tree indexes or to partitioned index-organized tables.

Table 4-3 lists maintenance operations that can be performed on index partitions, and indicates on which type of index (global or local) they can be performed. The ALTER INDEX clause used for the maintenance operation is shown.

Global indexes do not reflect the structure of the underlying table. If partitioned, they can be partitioned by range or hash.

Because local indexes reflect the underlying structure of the table, partitioning is maintained automatically when table partitions and subpartitions are affected by maintenance activity. Therefore, partition maintenance on local indexes is less necessary and there are fewer options.

Table 4-3 ALTER INDEX Maintenance Operations for Index Partitions

Maintenance Operation	Type of Index	Type of Index Partitioning
Maintenance Operation	Type of Index	Range	Hash and List	Composite
Adding Index Partitions	Global	`-`	`ADD PARTITION` (hash only)	-
	Local	N/A	N/A	N/A
Dropping Index Partitions	Global	`DROP PARTITION`	-	-
	Local	N/A	N/A	N/A
Modifying Default Attributes of Index Partitions	Global	`MODIFY DEFAULT ATTRIBUTES`	-	-
	Local	`MODIFY DEFAULT ATTRIBUTES`	`MODIFY DEFAULT ATTRIBUTES`	`MODIFY DEFAULT ATTRIBUTES` `MODIFY DEFAULT ATTRIBUTES FOR PARTITION`
Modifying Real Attributes of Index Partitions	Global	`MODIFY PARTITION`	-	-
	Local	`MODIFY PARTITION`	`MODIFY PARTITION`	`MODIFY PARTITION` `MODIFY SUBPARTITION`
About Rebuilding Index Partitions	Global	`REBUILD PARTITION`	-	-
	Local	`REBUILD PARTITION`	`REBUILD PARTITION`	`REBUILD SUBPARTITION`
About Renaming Index Partitions	Global	`RENAME PARTITION`	-	-
	Local	`RENAME PARTITION`	`RENAME PARTITION`	`RENAME PARTITION` `RENAME SUBPARTITION`
Splitting Index Partitions	Global	`SPLIT PARTITION`	-	-
	Local	N/A	N/A	N/A

These operations cannot be performed on reference-partitioned tables. If performed on a parent table, then these operations cascade to all descendant tables.