Rename a partition

After you create a partitioned table, you can rename a partition or subpartition of the table.

Considerations

• Only the partition of the primary table is renamed. The partition of the local index of the primary table is not renamed. However, after you rename a partition, if you perform an operation that rebuilds the local index, such as dropping a column of the partitioned table, the name of the partition of the local index becomes the same as that of the primary table.

• When you rename a partition, the new partition name cannot be the same as the name of an existing partition or subpartition in the table.

• Partition names are case-insensitive in OceanBase Database. Therefore, you cannot change the name of a partition to one that differs from that of another partition only in case.

Rename a partition

You can use the RENAME PARTITION clause to rename a partition. The syntax is as follows:

ALTER TABLE table_name RENAME PARTITION partition_name TO new_partition_name;

You can query the USER_TAB_PARTITIONS view to check whether the operation to rename a partition succeeds, or the USER_TAB_SUBPARTITIONS view to check whether the operation to rename a subpartition succeeds.

Here is an example:

1. Create a subpartitioned table named t2_f_rr without using a template.

   obclient> CREATE TABLE t2_f_rr(col1 INT,col2 INT)
       PARTITION BY RANGE(col1)
       SUBPARTITION BY RANGE(col2)
        (PARTITION p0 VALUES LESS THAN(100)
          (SUBPARTITION sp0 VALUES LESS THAN(2020),
           SUBPARTITION sp1 VALUES LESS THAN(2021)
          ),
         PARTITION p1 VALUES LESS THAN(200)
          (SUBPARTITION sp2 VALUES LESS THAN(2020),
           SUBPARTITION sp3 VALUES LESS THAN(2021),
           SUBPARTITION sp4 VALUES LESS THAN(2022)
           )
         );
   

2. Query the names of partitions and subpartitions.

   obclient> SELECT table_name,partition_name,subpartition_name FROM USER_TAB_SUBPARTITIONS WHERE table_name = 'T2_F_RR';
   

   The query result is as follows:

   +------------+----------------+-------------------+
   | TABLE_NAME | PARTITION_NAME | SUBPARTITION_NAME |
   +------------+----------------+-------------------+
   | T2_F_RR    | P0             | SP0               |
   | T2_F_RR    | P0             | SP1               |
   | T2_F_RR    | P1             | SP2               |
   | T2_F_RR    | P1             | SP3               |
   | T2_F_RR    | P1             | SP4               |
   +------------+----------------+-------------------+
   5 rows in set
   

3. Rename the P0 partition to NEW_P0.

   ALTER TABLE T2_F_RR RENAME PARTITION P0 TO NEW_P0;
   

4. Query the names of partitions and subpartitions again.

   obclient> SELECT table_name,partition_name,subpartition_name FROM SYS.USER_TAB_SUBPARTITIONS WHERE table_name = 'T2_F_RR';
   

   The query result is as follows:

   +------------+----------------+-------------------+
   | TABLE_NAME | PARTITION_NAME | SUBPARTITION_NAME |
   +------------+----------------+-------------------+
   | T2_F_RR    | NEW_P0         | SP0               |
   | T2_F_RR    | NEW_P0         | SP1               |
   | T2_F_RR    | P1             | SP2               |
   | T2_F_RR    | P1             | SP3               |
   | T2_F_RR    | P1             | SP4               |
   +------------+----------------+-------------------+
   5 rows in set
   

Rename a subpartition

The syntax for renaming a subpartition is as follows:

ALTER TABLE table_name RENAME SUBPARTITION partition_name TO new_partition_name;

After you rename a subpartition, you can query the USER_TAB_SUBPARTITIONS view to check whether the operation succeeds.

Here is an example:

1. Create a subpartitioned table named t3_f_rr without using a template.

   obclient> CREATE TABLE t3_f_rr(col1 INT,col2 INT)
       PARTITION BY RANGE(col1)
       SUBPARTITION BY RANGE(col2)
        (PARTITION p0 VALUES LESS THAN(100)
          (SUBPARTITION sp0 VALUES LESS THAN(2020),
           SUBPARTITION sp1 VALUES LESS THAN(2021)
          ),
         PARTITION p1 VALUES LESS THAN(200)
          (SUBPARTITION sp2 VALUES LESS THAN(2020),
           SUBPARTITION sp3 VALUES LESS THAN(2021),
           SUBPARTITION sp4 VALUES LESS THAN(2022)
           )
         );
   

2. Query the names of partitions and subpartitions.

   obclient> SELECT table_name,partition_name,subpartition_name FROM USER_TAB_SUBPARTITIONS WHERE table_name = 'T3_F_RR';
   

   The query result is as follows:

   +------------+----------------+-------------------+
   | TABLE_NAME | PARTITION_NAME | SUBPARTITION_NAME |
   +------------+----------------+-------------------+
   | T3_F_RR    | P0             | SP0               |
   | T3_F_RR    | P0             | SP1               |
   | T3_F_RR    | P1             | SP2               |
   | T3_F_RR    | P1             | SP3               |
   | T3_F_RR    | P1             | SP4               |
   +------------+----------------+-------------------+
   5 rows in set
   

3. Rename the SP1 subpartition to NEW_SP1.

   ALTER TABLE T3_F_RR RENAME SUBPARTITION SP1 TO NEW_SP1;
   

4. Query the names of partitions and subpartitions again.

   obclient> SELECT table_name,partition_name,subpartition_name FROM SYS.USER_TAB_SUBPARTITIONS WHERE table_name = 'T3_F_RR';
   

   The query result is as follows:

   +------------+----------------+-------------------+
   | TABLE_NAME | PARTITION_NAME | SUBPARTITION_NAME |
   +------------+----------------+-------------------+
   | T3_F_RR    | P0             | SP0               |
   | T3_F_RR    | P0             | NEW_SP1           |
   | T3_F_RR    | P1             | SP2               |
   | T3_F_RR    | P1             | SP3               |
   | T3_F_RR    | P1             | SP4               |
   +------------+----------------+-------------------+
   5 rows in set