Partitioned tables

After you create a partitioned table, you can add a partition to the table, drop a partition from the table, or truncate a partition for the table.

Add a partition

• Range or list partitioning

  For a range-partitioned table, you can add a partition only after the largest partition. You cannot add a partition at the start or in the middle of the range. If a table has a MAXVALUE partition, you cannot add new partitions to the table.

  For a list-partitioned table, you cannot add a partition that conflicts with previous partitions. If a list-partitioned table has a Default Partition, you cannot add new partitions to the table.

  To add a range or list partition in MySQL or Oracle mode, use the following syntax:

  ALTER TABLE table_name
    ADD PARTITION 
    (
      partition_defines
    )
  

  Adding a partition in a range- or list-partitioned table does not affect the use of global and local indexes.

• Hash or key partitioning

  In the current OceanBase Database version, you cannot add a partition to a hash- or key-partitioned table.

Drop a partition

You can drop multiple partitions at a time, but you cannot drop all the partitions in a table.

• Range or list partitioning

  When you drop a partition from a range- or list-partitioned table, the data in the partition is also dropped.

  To drop a partition in MySQL or Oracle mode, use the following syntax:

  obclient> ALTER TABLE table_name DROP PARTITION p1;
  
  obclient> ALTER TABLE table_name DROP PARTITION p1,p2;
  

  When you drop a partition, the data in the partition is also dropped. If you want to drop only the data, use the TRUNCATE statement.

  In addition, in Oracle mode, for a partitioned table that contains global indexes, when you drop a partition, you must add the UPDATE GLOBAL INDEXES keyword to the ALTER TABLE statement to update the global index information. For example:

  obclient> ALTER TABLE table_name DROP PARTITION p1
   UPDATE GLOBAL INDEXES;
  
  obclient> ALTER TABLE table_name DROP PARTITION p1,p2
   UPDATE GLOBAL INDEXES;
  

  Notice

  If you do not add the UPDATE GLOBAL INDEXES keyword, the global indexes in the partitioned table will become unavailable after the partition is dropped.

• Hash or key partitioning

  In the current OceanBase Database version, you cannot drop a partition from a hash- or key-partitioned table.

Truncate a partition

In the current OceanBase Database version, you can truncate one or more range or list partitions to clear all data from the partitions.

To truncate a partition in MySQL or Oracle mode, use the following syntax:

obclient> ALTER TABLE table_name TRUNCATE PARTITION p1;

obclient> ALTER TABLE table_name TRUNCATE PARTITION p1,p2;

In addition, in Oracle mode, for a partitioned table that contains global indexes, when you truncate a partition, you must add the UPDATE GLOBAL INDEXES keyword to the ALTER TABLE statement to update the global index information. For example:

obclient> ALTER TABLE table_name TRUNCATE PARTITION p1 UPDATE GLOBAL INDEXES;

obclient> ALTER TABLE table_name TRUNCATE PARTITION p1,p2 UPDATE GLOBAL INDEXES;

Notice

If you do not add the UPDATE GLOBAL INDEXES keyword, the global indexes in the partitioned table will become unavailable after the partition is truncated.