ALTER TABLE SUBPARTITION

Description

Modifies partitions of a level-2 partitioned table, including adding, deleting, clearing, splitting, merging, exchanging, renaming partitions, moving partition tablespaces, 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 a range partition is added, the key value of the added partition must be greater than the upper limit of the last range partition in the partitioned table. To add a partition to a table with the MAXVALUE partition, you are advised to use the SPLIT syntax.
• If a list partition is added, the key value of the added partition cannot be the same as that of an existing partition. To add a partition to a table with the DEFAULT partition, you are advised to use the SPLIT syntax.
• Hash partitions cannot be added. 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.
• If the number of partitions in the target partitioned table has reached the maximum (1048575), partitions cannot be added.
• If the partitioned table contains only one level-1 or level-2 partition, the partition cannot be deleted.
• Hash partitions 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.
• Only level-2 partitions (leaf nodes) can be split. Only range and list partitioning policies can be used and hash partitioning policies are not supported.
• Only level-2 partitions (leaf nodes) can be merged, and the source partitions must belong to the same level-1 partition.
• 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.

Syntax

Modifying a partition in a level-2 partitioned table includes modifying the table partition itself and the table partition name, and resetting the partition ID.

• Modify the syntax of the table partition.

  ALTER TABLE [ IF EXISTS ] { 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, GaussDB executes DROP PARTITION and ADD PARTITION first, and then the rest clauses in sequence.

      move_clause  |
      exchange_clause  |
      row_clause  |
      merge_clause  |
      modify_clause  |
      add_clause    |
      drop_clause   |    
      split_clause  |
      truncate_clause

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

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

  • The exchange_clause syntax is used to move the data from a general 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 ] [ VERBOSE ] [ 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 partition should be consistent.
    • The ordinary table cannot be a temporary table, and the partitioned table should be a level-2 partitioned table.
    • Ordinary tables and partitioned tables do not support dynamic data masking and row-level security constraints.
    

    • 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 row_clause syntax is used to set row movement of a partitioned table.

    { ENABLE | DISABLE } ROW MOVEMENT

  • The merge_clause syntax is used to merge partitions into one. The maximum number of source partitions that can be merged in a command is 300.

    MERGE SUBPARTITIONS { subpartition_name } [, ...] INTO SUBPARTITION partition_name 
        [ TABLESPACE tablespacename ] [ UPDATE GLOBAL INDEX ]

    

    For range partitioning, the ranges of the source partitions must increase continuously, and the partition name after MERGE can be the same as the name of the last source partition. For list partitioning, there is no such range requirement on the source partitions, and the partition name after MERGE can be the same as that of any source partition. If the partition name after MERGE is the same as that of a source partition, they are considered as the same partition.

    

    Ustore tables do not support ALTER TABLE MERGE SUBPARTITIONS in transaction blocks and stored procedures.

  • The modify_clause syntax is used to set whether a partitioned index is usable. The syntax can be used in level-1 partitions.

    MODIFY PARTITION partition_name { UNUSABLE LOCAL INDEXES | REBUILD UNUSABLE LOCAL INDEXES }

    It can also be used in level-2 partitions.

    MODIFY SUBPARTITION partition_name { UNUSABLE LOCAL INDEXES | REBUILD UNUSABLE LOCAL INDEXES }

  • The add_clause syntax is used to add one or more partitions to a specified partitioned table. The syntax can be used in level-1 partitions.

    ADD {partition_less_than_item | partition_list_item } [ ( subpartition_definition_list ) ]

    It can also be used in level-2 partitions.

    MODIFY PARTITION partition_name ADD subpartition_definition

    partition_less_than_item defines a range partition. The syntax is as follows:

    PARTITION partition_name VALUES LESS THAN ( partition_value | MAXVALUE ) [ TABLESPACE tablespacename ]

    partition_list_item defines a list partition. The syntax is as follows:

    PARTITION partition_name VALUES ( partition_value [, ...] | DEFAULT ) [ TABLESPACE tablespacename ]

    subpartition_definition_list contains the subpartition_definition object of one or more level-2 partitions. The syntax is as follows:

    SUBPARTITION subpartition_name [ VALUES LESS THAN ( partition_value | MAXVALUE ) | VALUES ( partition_value [, ...] | DEFAULT )]  [ TABLESPACE tablespace ]

    

    If the level-1 partition is a hash partition, you cannot use ADD to add a level-1 partition. If the level-2 partition is a hash partition, you cannot use MODIFY to add a level-2 partition.

  • The drop_clause syntax is used to remove a partition from a specified partitioned table. The syntax can be used in level-1 partitions.

    DROP PARTITION  { partition_name | FOR (  partition_value )  } [ UPDATE GLOBAL INDEX ]

    It can also be used in level-2 partitions.

    DROP SUBPARTITION  { subpartition_name | FOR (  partition_value, subpartition_value )  } [ UPDATE GLOBAL INDEX ]

    

    • If the level-1 partition is a hash partition, the level-1 partition cannot be deleted. If the level-2 partition is a hash partition, the level-2 partition cannot be deleted.
    • At least one sub-partition must be retained.
  • The split_clause syntax is used to split one partition into partitions.

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

    

    • The partition name after SPLIT can be the same as the source partition name, but they are regarded as different partitions.
    • The syntax for specifying the split point for range partitioning is as follows:

      AT ( subpartition_value ) INTO ( SUBPARTITION subpartition_name [ TABLESPACE tablespacename ] , SUBPARTITION subpartition_name [ TABLESPACE tablespacename ] )

      

      The split point must be in the range of partition keys of the partition to be split. Specifying a split point can only split one partition into two partitions.

    • The syntax of not specifying the split point for range partitioning is as follows (the range of the last partition is not defined, that is, the VALUES LESS THAN (subpartition_value) part is not defined; by default, the last partition inherits the upper boundary value of the range defined for the source partition):

      INTO ( SUBPARTITION subpartition_name VALUES LESS THAN (subpartition_value) [ TABLESPACE tablespacename ][, ...] )

      

      • The range defined for the first new partition must be greater than that of the previous partition (if any) of the partition being split.
      • The range of the last new partition cannot be defined and it inherits the upper boundary value of the range defined for the source partition by default.
      • The new partition must meet the constraint of continuously increasing range.
    • The syntax for specifying the split point for list-range partitioning is as follows:

      VALUES ( subpartition_value ) INTO ( SUBPARTITION subpartition_name [ TABLESPACE tablespacename ] , SUBPARTITION subpartition_name [ TABLESPACE tablespacename ] )

      

      The split point must be a non-empty true subset of the source partition. Specifying a split point can only split one partition into two partitions.

    • The syntax for not specifying the split point for a list partitioned table is as follows:

      INTO ( SUBPARTITION subpartition_name VALUES (subpartition_value_list) [ TABLESPACE tablespacename ][, ...] )

      

      • The range of the last partition is not defined, that is, the VALUES (partition_value_list) part is not defined; the partition range is equal to the remaining set of the source partition excluding other level-2 partitions.
      • If no split point is specified, each new partition must be a non-empty true subset of the source partition and does not overlap with each other.
  • 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  { 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 name.

  Value range: an existing level-2 partition name.

• tablespacename

  Specifies which tablespace the partition moves to.

  Value range: an existing tablespace 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 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.

• VERBOSE

  When VALIDATION is WITH, if the ordinary table contains data that is out of the partition key range, insert the data to the correct partition. If there is no correct partition where the data can be inserted to, an error is reported.

  

  Only when VALIDATION is WITH, VERBOSE can be specified.

• partition_new_name

  Specifies the new name of a partition.

  Value range: a string. It must comply with the naming convention.

• subpartition_new_name

  Specifies the new name of a level-2 partition.

  Value range: a string. It must comply with the naming convention.

• UPDATE GLOBAL INDEX

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

Examples

See the examples in section "CREATE TABLE SUBPARTITION."