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 (These operations cannot be performed on reference-partitioned tables. If performed on a parent table, then these operations cascade to all descendant tables.)

Coalescing Partitions, see About Coalescing Partitions and Subpartitions

N/A

N/A

COALESCE PARTITION

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.)

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 (These operations cannot be performed on reference-partitioned tables. If performed on a parent table, then these operations cascade to all descendant tables.)

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 (These operations cannot be performed on reference-partitioned tables. If performed on a parent table, then these operations cascade to all descendant tables.)

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 (These operations cannot be performed on reference-partitioned tables. If performed on a parent table, then these operations cascade to all descendant tables.)

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
Range Hash and List Composite

Adding Index Partitions

Global

-

ADD PARTITION (hash only)

-

Adding Index Partitions

Local

N/A

N/A

N/A

Dropping Index Partitions

Global

DROP PARTITION

-

-

Dropping Index Partitions

Local

N/A

N/A

N/A

Modifying Default Attributes of Index Partitions

Global

MODIFY DEFAULT ATTRIBUTES

-

-

Modifying Default Attributes of Index Partitions

Local

MODIFY DEFAULT ATTRIBUTES

MODIFY DEFAULT ATTRIBUTES

MODIFY DEFAULT ATTRIBUTES

MODIFY DEFAULT ATTRIBUTES FOR PARTITION

Modifying Real Attributes of Index Partitions

Global

MODIFY PARTITION

-

-

Modifying Real Attributes of Index Partitions

Local

MODIFY PARTITION

MODIFY PARTITION

MODIFY PARTITION

MODIFY SUBPARTITION

About Rebuilding Index Partitions

Global

REBUILD PARTITION

-

-

About Rebuilding Index Partitions

Local

REBUILD PARTITION

REBUILD PARTITION

REBUILD SUBPARTITION

About Renaming Index Partitions

Global

RENAME PARTITION

-

-

About Renaming Index Partitions

Local

RENAME PARTITION

RENAME PARTITION

RENAME PARTITION

RENAME SUBPARTITION

Splitting Index Partitions

Global

SPLIT PARTITION

-

-

Splitting Index Partitions

Local

N/A

N/A

N/A