Partition maintenance Overview

This topic describes how to maintain partitions and subpartitions for Oracle tables and indexes.

This topic describes how to maintain partitions and subpartitions for Oracle tables and indexes.

This topic describes how to maintain partitions and subpartitions for Oracle tables and indexes.

This topic includes the following topics:

Note:

The following sections describe how to maintain a partition table. These maintenance operations affect the availability of indexes or index partitions. Consider the following:

Only non-empty indexes and index partitions can be identified as UNUSABLE. If they are empty, the USABLE/UNUSABLE status remains unchanged.

Only indexes or index partitions in the available status can be updated by subsequent DML updates.

Maintenance operations that can be performed on partitions

Table 4-1 lists the maintenance operations that can be performed on partition tables and composite partition tables. Table 4-2 lists the maintenance operations for sub-partitions that can be performed on a partition table. For each type of partition and partition, specific clauses of the alter table statement used in maintenance operations are listed.

Alter table maintenance for TABLE 4-1 Partitioned Tables

Maintenance Operations

Compound range

Range -*

Interval Compound

Interval -*

Hash

List Compound

List -*

Reference

Add Partition

ADD PARTITION

ADD PARTITION

ADD PARTITION

ADD PARTITION

N/

Merge partitions

N/

N/

COALESCE PARTITION

N/

N/

Delete Partition

DROP PARTITION

DROP PARTITION

N/

DROP PARTITION

N/

Swap Partition

EXCHANGE PARTITION

EXCHANGE PARTITION

EXCHANGE PARTITION

EXCHANGE PARTITION

EXCHANGE PARTITION

Merge partitions

MERGE PARTITIONS

MERGE PARTITIONS

N/

MERGE PARTITIONS

N/

Modify default attributes

MODIFY DEFAULT ATTRIBUTES

MODIFY DEFAULT ATTRIBUTES

MODIFY DEFAULT ATTRIBUTES

MODIFY DEFAULT ATTRIBUTES

MODIFY DEFAULT ATTRIBUTES

Modify the current attribute of a partition.

MODIFY PARTITION

MODIFY PARTITION

MODIFY PARTITION

MODIFY PARTITION

MODIFY PARTITION

Modify list partition: added value

N/

N/

N/

Modify partition... ADD VALUES

N/

Modify list partition: delete Value

N/

N/

N/

Modify partition... DROP VALUES

N/

Move a partition

MOVE SUBPARTITION

MOVE SUBPARTITION

MOVE PARTITION

MOVE SUBPARTITION

MOVE PARTITION

Rename a partition

RENAME PARTITION

RENAME PARTITION

RENAME PARTITION

RENAME PARTITION

RENAME PARTITION

Partition

SPLIT PARTITION

SPLIT PARTITION

N/

SPLIT PARTITION

N/

Partition Truncation

TRUNCATE PARTITION

TRUNCATE PARTITION

TRUNCATE PARTITION

TRUNCATE PARTITION

TRUNCATE PARTITION

Alter table maintenance for TABLE 4-2 sub-partition tables

Maintenance Operations

Compound

*-Range

Compound

*-Hash

Compound

*-List

Add Partition

Modify partition... ADD SUBPARTITION

Modify partition... ADD SUBPARTITION

Modify partition... ADD SUBPARTITION

Merge partitions

N/

Modify partition... COALESCE SUBPARTITION

N/

Delete Partition

DROP SUBPARTITION

N/

DROP SUBPARTITION

Swap Partition

EXCHANGE SUBPARTITION

N/

EXCHANGE SUBPARTITION

Merge partitions

MERGE SUBPARTITIONS

N/

MERGE SUBPARTITIONS

Modify default attributes

MODIFY DEFAULT ATTRIBUTES FOR PARTITION

MODIFY DEFAULT ATTRIBUTES FOR PARTITION

MODIFY DEFAULT ATTRIBUTES FOR PARTITION

Modify the current attribute of a partition.

MODIFY SUBPARTITION

MODIFY SUBPARTITION

MODIFY SUBPARTITION

Modify list partition: added value

N/

N/

Modify subpartition... ADD VALUES

Modify list partition: delete Value

N/

N/

Modify subpartition... DROP VALUES

Modify a subpartition Template

SET SUBPARTITION TEMPLATE

SET SUBPARTITION TEMPLATE

SET SUBPARTITION TEMPLATE

Move a partition

MOVE SUBPARTITION

MOVE SUBPARTITION

MOVE SUBPARTITION

Rename a partition

RENAME SUBPARTITION

RENAME SUBPARTITION

RENAME SUBPARTITION

Partition

SPLIT SUBPARTITION

N/

SPLIT SUBPARTITION

Partition Truncation

TRUNCATE SUBPARTITION

TRUNCATE SUBPARTITION

TRUNCATE SUBPARTITION

Note:

When you use table compression for the first time, introduce the compressed partition into a partition table that contains a bitmap index and only contains uncompressed partitions, you must do the following:

Delete all existing Bitmap indexes and bitmap partition indexes, or mark them as UNUSABLE;

Set table compression attributes

Re-Indexing

Whether a partition contains data is independent from the operations that introduce compressed partitions.

In addition, this does not apply to partition tables that contain B-tree indexes and partition index organization tables.

Table 4-3 lists the maintenance operations that can be performed on index partitions, and lists the index types (global or local) that can be operated ). The alter index clause used for maintenance operations is also listed.

The global index does not reflect the structure of the Base table. If partitions exist, they can be partitioned by range or hash. The global partition index can share some operations on the partition table, not all.

Because the local index reflects the structure of the Base table, index partitions are automatically maintained during maintenance of partitions or subpartitions. Therefore, the necessity of maintaining local index partitions is greatly reduced, and there are few options.

Table 4-3 alter index maintenance for INDEX partitions

Maintenance Operations

Index type

Index partition type

Range

Hash and list

Compound

Add an index Partition

Global

-

Add partition (hash only)

-

Local

N/

N/

N/

Delete index partitions

Global

DROP PARTITION

-

-

Local

N/

N/

N/

Modify the default attributes of an index Partition

Global

MODIFY DEFAULT ATTRIBUTES

-

-

Local

MODIFY DEFAULT ATTRIBUTES

MODIFY DEFAULT ATTRIBUTES

MODIFY DEFAULT ATTRIBUTES

MODIFY DEFAULT ATTRIBUTES FOR PARTITION

Modify the current attribute of an index Partition

Global

MODIFY PARTITION

-

-

Local

MODIFY PARTITION

MODIFY PARTITION

MODIFY PARTITION

MODIFY SUBPARTITION

Re-create index partitions

Global

REBUILD PARTITION

-

-

Local

REBUILD PARTITION

REBUILD PARTITION

REBUILD SUBPARTITION

Rename an index Partition

Global

RENAME PARTITION

-

-

Local

RENAME PARTITION

RENAME PARTITION

RENAME PARTITION

RENAME SUBPARTITION

Split index partitions

Global

SPLIT PARTITION

-

-

Local

N/

N/

N/

Automatic Index Update

Before discussing the maintenance operations for partition tables and INDEXES, it is very important to discuss the impact of the update indexes clause that can be specified in the alter table statement.