ALTER TABLE SUBPARTITION

Description

Modifies partitions of a level-2 partitioned table, including adding, deleting, clearing, splitting, merging, exchanging, and renaming partitions, moving partition tablespaces, enabling and disabling the automatic partitioning function, and modifying partition attributes.

Precautions

• The tablespace of the added partition cannot be pg_global.
• The name of the added partition must be different from the names of the existing level-1 and level-2 partitions in the partitioned table.
• The key value of the added partition must be consistent with the type of partition keys in the partitioned table.
• If the number of partitions in the target partitioned table has reached the maximum (1048575), partitions cannot be added.
• If a partitioned table has only one level-1 or level-2 partition, the partition cannot be deleted.
• PARTITION FOR() and SUBPARTITION FOR() can be used to select partitions. The number of specified values in the brackets must be the same as the number of columns defined in partition creation.
• In M-compatible mode, SUBPARTITION supports only hash and key partitions, but does not support adding hash and key partitions. However, if the level-2 partition mode of an level-2 partitioned table is hash but the level-1 partition mode is not hash, you can add a level-1 partition and create the corresponding level-2 partition. Hash and key partitions cannot be deleted, split, or merged.
• Only the owner of a partitioned table or users granted with the ALTER permission on the partitioned table can run the ALTER TABLE PARTITION command. The system administrator has the permission to run the command by default.
• Deleting, splitting, clearing, and exchanging partitions will invalidate the global index. The UPDATE GLOBAL INDEX clause can be used to update the index synchronously.
• If the UPDATE GLOBAL INDEX clause is not used when you delete, split, clear, or exchange partitions, concurrent DML services may report errors due to invalidated indexes.
• If enable_gpi_auto_update is set to on, the global index is automatically updated even if the UPDATE GLOBAL INDEX clause is not declared.
• To enable automatic extension of level-1 and level-2 list partitions, ensure that no DEFAULT partition key exists in the partitions at the corresponding level.

Syntax

Modifying partitions in a level-2 partitioned table includes modifying the main syntax of a table partition, modifying the syntax of a table partition name, resetting the partition ID, and enabling or disabling the syntax of the automatic partitioning function.

• Modify the syntax of the table partition.

  ALTER TABLE { table_name  [*] | ONLY table_name | ONLY ( table_name  )}
      action [, ... ];

  action indicates the following clauses for maintaining partitions. For the partition continuity when multiple clauses are used for partition maintenance, M-compatible databases execute DROP PARTITION and ADD PARTITION first, and then the rest clauses in sequence.

      move_clause  |
      exchange_clause  |
      truncate_clause  |
      set_partitioning_clause

  • The move_clause syntax is used to move the partition to a new tablespace.

    MOVE SUBPARTITION { subpartition_name | FOR ( subpartition_value [, ...] ) } TABLESPACE tablespacename

  • The exchange_clause syntax is used to move the data from an ordinary table to a specified partition.

    EXCHANGE SUBPARTITION { ( subpartition_name ) | FOR ( subpartition_value [, ...] ) } 
        WITH TABLE {[ ONLY ] ordinary_table_name | ordinary_table_name * | ONLY ( ordinary_table_name )} 
        [ { WITH | WITHOUT } VALIDATION ] [ UPDATE GLOBAL INDEX ]

    The ordinary table and partition whose data is to be exchanged must meet the following requirements:

    • The number of columns of the ordinary table is the same as that of the partition, and their information should be consistent, including column name, data type, constraint, collation information, storage parameter, and compression information.
    • The compression information of the ordinary table and partitioned table should be consistent.
    • The number of ordinary table indexes is the same as that of local indexes of the partition, and the index information is the same.
    • The number and information of constraints of the ordinary table and partitioned table should be consistent.
    • The ordinary table cannot be a temporary table, and the partitioned table should be a level-2 partitioned table.
    

    • When the exchange is done, the data and tablespace of the ordinary table and partitioned table are exchanged. In this case, the statistics on the ordinary table and partition become unreliable, and they should be analyzed again.
    • A non-partition key cannot be used to create a local unique index. Therefore, if an ordinary table contains a unique index, data cannot be exchanged.

      To exchange data, you can create an intermediate table, insert partition data into the intermediate table, truncate partitions, insert ordinary table data into the partitioned table, drop the ordinary table, and rename the intermediate table.

    • If the DROP COLUMN operation is performed on an ordinary or partitioned table, the deleted column still exists physically. Therefore, you need to ensure that the deleted column of the ordinary table is strictly aligned with that of the partition. Otherwise, the exchange will fail.
  • The truncate_clause syntax is used to remove a specified partition from a partitioned table. The syntax can be used in level-1 partitions.

    TRUNCATE PARTITION  { { ALL | partition_name [, ...] } | FOR (  partition_value [, ...] )  } [ UPDATE GLOBAL INDEX ]

    It can also be used in level-2 partitions.

    TRUNCATE SUBPARTITION  { subpartition_name | FOR (  subpartition_value [, ...] )  } [ UPDATE GLOBAL INDEX ]

Parameters

• table_name

  Specifies the name of a partitioned table.

  Value range: an existing partitioned table name

• subpartition_name

  Specifies the name of a level-2 partition.

  Value range: an existing level-2 partition name

• tablespacename

  Specifies which tablespace the partition moves to.

  Value range: an existing tablespace name

  Note: You need to create or delete tablespaces in a non-M-compatible database.

• { ALL | partition_name [, ...] }

  ALL: clears all partition data.

  partition_name: specifies a partition in the target partitioned table. Level-1 and level-2 partition names are supported.

  Value range: an existing partition name.

• partition_value

  Specifies the key value of the level-1 partition.

  The value specified by PARTITION FOR ( partition_value [, ...] ) can uniquely identify a level-1 partition.

  Value range: partition keys for the level-1 partition to be operated.

• subpartition_value

  Specifies the level-1 and level-2 partition key values.

  The value specified by SUBPARTITION FOR ( subpartition_value [, ...] ) can uniquely identify a level-2 partition.

  Value range: partition key values of the level-1 and level-2 partitions for the level-2 partition to be operated.

• UNUSABLE LOCAL INDEXES

  Sets all the indexes unusable in the partition.

• REBUILD UNUSABLE LOCAL INDEXES

  Rebuilds all the indexes in the partition.

• { ENABLE | DISABLE } ROW MOVEMET

  Specifies whether to enable row movement.

  If the tuple value is updated on the partition key during the UPDATE operation, the partition where the tuple is located is altered. Setting this parameter enables error messages to be reported or movement of the tuple between partitions.

  Value range:

  • ENABLE: Row movement is enabled.
  • DISABLE: Row movement is disabled.

  By default, this function is enabled.

• ordinary_table_name

  Specifies the name of the ordinary table whose data is to be migrated.

  Value range: an existing ordinary table name.

• { WITH | WITHOUT } VALIDATION

  Checks whether the ordinary table data meets the specified partition key range of the partition to be migrated.

  Value range:

  • WITH: checks whether the ordinary table data meets the partition key range of the partition to be migrated. If any data does not meet the required range, an error is reported.
  • WITHOUT: does not check whether the ordinary table data meets the partition key range of the partition to be migrated.

  The default value is WITH.

  The check is time consuming, especially when the data volume is large. Therefore, use WITHOUT when you are sure that the current ordinary table data meets the partition key range of the partition to be migrated.

• partition_new_name

  Specifies the new name of a partition.

  Value range: a string complying with Identifier Description.

• subpartition_new_name

  Specifies the new name of a level-2 partition.

  Value range: a string complying with Identifier Description.

• UPDATE GLOBAL INDEX

  If this parameter is specified, all global indexes in the partitioned table will be updated to ensure that data can be queried properly using global indexes. If this parameter is not specified, all global indexes in the partitioned table will become invalid.

Examples

See examples in CREATE TABLE SUBPARTITION.

Helpful Links

CREATE TABLE SUBPARTITION