Partition operations

This topic describes the supported conversion scope of ALTER TABLE DDL operations for partition operations during data migration from a MySQL database to the MySQL compatible mode 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 partition operations

• Use the ADD PARTITION clause to add RANGE partitions.

• Use the ADD PARTITION clause to add LIST partitions.

• Use the DROP PARTITION clause to drop partitions.

• Use the partition name to specify which partition to drop. Example:

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

• Use the ALTER TABLE...PARTITION BY... clause to redefine partitions. Example:

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

  Notice

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

Unsupported partition operations

• Use the ADD PARTITION clause to add HASH partitions.

• Use the TRUNCATE PARTITION clause.

• Use the DISCARD PARTITION clause.

• Use the IMPORT PARTITION clause.

• Use the COALESCE PARTITION clause.

• Use the REORGANIZE PARTITION clause.

• Use the EXCHANGE PARTITION clause.

• Use the ANALYZE PARTITION clause.

• Use the CHECK PARTITION clause.

• Use the OPTIMIZE PARTITION clause.

• Use the REBUILD PARTITION clause.

• Use the REPAIR PARTITION clause.

• Use the REMOVE PARTITIONING clause.