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;

After you rename a partition, you can query the USER_TAB_PARTITIONS view for a partitioned table or the USER_TAB_SUBPARTITIONS view for a subpartitioned table to verify that the partition name was modified successfully.

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. After the table is created, query the partition names.

   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 partition P0 to NEW_P0.

   ALTER TABLE T2_F_RR RENAME PARTITION P0 TO NEW_P0;
   

4. View the partition names again to verify the modification.

   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 verify that the partition name was modified successfully.

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. After the table is created, view the partition names.

   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 subpartition SP1 to NEW_SP1.

   ALTER TABLE T3_F_RR RENAME SUBPARTITION SP1 TO NEW_SP1;
   

4. View the partition names again to verify the modification.

   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