Oracle 11g maintenance partition (2) -- Coalescing Partitions

Oracle 11g maintenance partition (2) -- Coalescing Partitions

Integrated Partition

Converged partitioning is a method to reduce the number of partitions in a hash partition table or index. It can also be used to reduce the number of subpartitions in a composite hash partition table. When a hash partition is merged, its content is re-allocated to one or more other partitions Based on the hash function. The merged partition is selected by the database and will be deleted after its content is redistributed. If you perform the fusion partition operation in the base table that references the partition table definition, the referenced partition table will automatically inherit the new partition definition.

The index may be identified as UNUSABLE, as described in the following table:

Table type	Index operations
Normal table (heap table)	Unless you specify the update indexes statement in the alter table statement: L The local indexes of new and re-allocated existing partitions are identified as UNUSABLE and must be rebuilt. L all global indexes, or all partitions of the global index are identified as UNUSABLE and must be rebuilt.
Index organization table	L local indexes are processed in the same way as normal tables. L all global indexes are available;

Add a partition to the hash partition table.

We use the alter table... coalesce partition statement to combine a PARTITION in the hash partition table. The following statement combines a partition to reduce the number of partitions in Table ouu1.

Alter table ouu1

Coalesce partition;

Integrate sub-partitions into a composite hash Partition Table

The following statement re-distributes the content of a subpartition in the us_locations partition to one or more subpartitions in the same partition (determined by the hash function). Note that, for an interval partition table, you can only combine the hash subpartitions of materialized range partitions or materialized interval partitions. Basically, this operation can be regarded as the inverse operation of modify partition... add subpartition.

Alter table diving modify partition us_locations

Coalesce subpartition;

Integrated Global hash partition Index

In a global hash partition index, we can use the coalesce partition clause of the alter index statement to reduce an index partition. The database selects partitions based on the requirements of hash partitions. The following statement reduces the number of partitions in the index hgidx.

Alter index hgidx coalesce partition;