Adding a Partition to a Hash Partitioned Table

When you add a partition to a hash partitioned table, the database populates the new partition with rows rehashed from an existing partition (selected by the database) as determined by the hash function. Consequently, if the table contains data, then it may take some time to add a hash partition.

The following statements show two ways of adding a hash partition to table scubagear. Choosing the first statement adds a new hash partition whose partition name is system generated, and which is placed in the default tablespace. The second statement also adds a new hash partition, but that partition is explicitly named p_named and is created in tablespace gear5.

ALTER TABLE scubagear ADD PARTITION;

ALTER TABLE scubagear
      ADD PARTITION p_named TABLESPACE gear5;

Indexes may be marked UNUSABLE as explained in the following table:

Table Type Index Behavior

Regular (Heap)

Unless you specify UPDATE INDEXES as part of the ALTER TABLE statement:

  • The local indexes for the new partition, and for the existing partition from which rows were redistributed, are marked UNUSABLE and must be rebuilt.

  • All global indexes, or all partitions of partitioned global indexes, are marked UNUSABLE and must be rebuilt.

Index-organized

  • For local indexes, the behavior is identical to heap tables.

  • All global indexes remain usable.