Operations on partitions

This topic describes the supported conversion scope of ALTER TABLE DDL operations on partitions during data migration from a MySQL database to a MySQL tenant of OceanBase Database.

Syntax

ALTER TABLE tbl_name partition_options

partition_options:
    partition_option [partition_option] ...

partition_option: {
    ADD PARTITION (partition_definition)
  | DROP PARTITION partition_names
  | DISCARD PARTITION {partition_names | ALL} TABLESPACE
  | IMPORT PARTITION {partition_names | ALL} TABLESPACE
  | TRUNCATE PARTITION {partition_names | ALL}
  | COALESCE PARTITION number
  | REORGANIZE PARTITION partition_names INTO (partition_definitions)
  | EXCHANGE PARTITION partition_name WITH TABLE tbl_name [{WITH | WITHOUT} VALIDATION]
  | ANALYZE PARTITION {partition_names | ALL}
  | CHECK PARTITION {partition_names | ALL}
  | OPTIMIZE PARTITION {partition_names | ALL}
  | REBUILD PARTITION {partition_names | ALL}
  | REPAIR PARTITION {partition_names | ALL}
  | REMOVE PARTITIONING
}

Supported operations

• Use ADD PARTITION to add a RANGE partition.

• Use ADD PARTITION to add a LIST partition.

• Use DROP PARTITION to drop a partition.

• Specify the partition name to drop rows from the partition. Example:

  ALTER TABLE T TRUNCATE PARTITION P0;
  ALTER TABLE T TRUNCATE PARTITIONS P0,P1;
  

• Use ALTER TABLE...PARTITION BY... to redefine a partition. Example:

  ALTER TABLE orders PARTITION BY HASH(order_id) PARTITIONS 3;
  

  Notice

  Only OceanBase Database V4.0.0 and later versions support this operation.

Unsupported operations

• Use ADD PARTITION to add a HASH partition.

• TRUNCATE PARTITION is not supported.

• Use DISCARD PARTITION.

• Use IMPORT PARTITION.

• Use COALESCE PARTITION.

• Use REORGANIZE PARTITION.

• Use EXCHANGE PARTITION.

• Use ANALYZE PARTITION.

• Use CHECK PARTITION.

• Use OPTIMIZE PARTITION.

• Use REBUILD PARTITION.

• Use REPAIR PARTITION.

• Use REMOVE PARTITIONING.