ALTER TABLE SUBPARTITION

Function

ALTER TABLE SUBPARTITION modifies partitions from a level-2 partitioned table, including clearing the partitions and splitting the partitions.

Precautions

• Currently, partitions from a level-2 partitioned table can be cleared or split only.
• Only level-2 partitions (leaf nodes) can be split and only the range and list partitioning policies can be used. The list partitioning policy can be used only when default partitions are split.
• Only the partitioned table owner or a user granted with the ALTER permission can run the ALTER TABLE PARTITION command. The system administrator has this permission by default.

Syntax

• Modify the syntax of the table partition.

  AlterTable ::= ALTER TABLE [ IF EXISTS ] { table_name  [*] | ONLY table_name | ONLY ( table_name  )}
      action [, ... ];

  action indicates the following clauses for maintaining partitions.

  action::= split_clause  |
     truncate_clause

• The split_clause syntax is used to split one partition into different partitions.

  split_clause ::= SPLIT SUBPARTITION { subpartition_name} { split_point_clause  } [ UPDATE GLOBAL INDEX ]

  The split_point_clause syntax is used to specify a split point.

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

  NOTICE:

  • The size of split point should be in the range of splitting partition key.
  • One partition can be split into only two new partitions.

• The truncate_clause syntax is used to remove a specified partition from a partitioned table.

  truncate_clause ::= TRUNCATE SUBPARTITION  { subpartition_name } [ UPDATE GLOBAL INDEX ]

Parameter Description

• 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

Examples

See the examples in CREATE TABLE SUBPARTITION.