Partition exchange

The partition exchange feature of OceanBase Database is primarily used to quickly import data into a partitioned table, achieving efficient data loading. Specifically, it first rapidly imports the data into a new non-partitioned table via full direct load, and then uses the partition exchange feature to quickly import the data from the non-partitioned table into the partitioned table, thereby accelerating the performance of incremental data import for existing partitioned tables.

You can consider using the partition exchange feature as an alternative approach for incremental direct load. For example, to import incremental data into partitioned table A using the partition exchange feature, follow these steps:

1. Create an empty new partition P on table A.
2. Create a new non-partitioned table B with exactly the same structure as table A, and import the incremental data into table B via direct load.
3. Use the partition exchange feature to swap the new partition P in table A with table B.

Supported partition exchange scenarios

The following table describes the supported partition exchange scenarios. In the table, a row represents the source table (origin_table_name) and a column represents the target table (target_partition_table_name).

Limitations and considerations

• Data validation is not supported. You must specify WITHOUT VALIDATION, and you must ensure the validity of the data to be exchanged.

• The table-level and column-level attributes of the partitioned table and the non-partitioned table must correspond one by one.

• The constraint requirements of the partitioned table and the non-partitioned table are exactly the same. Moreover, foreign key constraints between the two tables are not supported in the current version.

• The local index tables of the partitioned table and the index tables of the non-partitioned table must correspond one by one.

• In MySQL-compatible mode, the partition exchange requires that the index definitions of the source table and the target table are consistent (including index name, type, columns, and their order). OceanBase Database cannot currently identify the following inconsistencies:

  • Different prefix lengths of prefix indexes.
  • Inconsistent expressions of virtual generated columns.

  Note

  For scenarios where OceanBase Database can identify inconsistencies between the indexes of the source table and the target table, the partition exchange operation will be rejected. For scenarios not covered above, verification may be omitted. It is recommended that you ensure the index definitions are identical to avoid potential data or query anomalies.

• Only the behavior of INCLUDING INDEXES is supported (the default in MySQL-compatible mode includes the INCLUDING INDEXES behavior). Data corresponding to partitions with local indexes will also be involved in the exchange, and the local indexes remain available after the exchange as they were before.

• UPDATE GLOBAL INDEXES is not supported. Therefore, global indexes will be in the Unusable state (invalid state) after a successful exchange.

• The current partition exchange feature is recommended to be used only as a temporary replacement for incremental direct load. For information about incremental direct load, see Overview of direct load.

• When exchanging data between a partitioned table and a non-partitioned table:

  • The partition must be a RANGE/RANGE COLUMNS/LIST partition.

• When exchanging data between subpartitions of a partitioned table and a non-partitioned table:

  • No requirement for the type of the partition, but the subpartition must be a RANGE/RANGE COLUMNS partition.

• When exchanging data between partitions of a partitioned table and partitions of another partitioned table:

  • target_partition_table_name must be a partitioned table, and the specified partition to exchange must be a partition name of that table. Moreover, the partition of this partitioned table must be a RANGE/RANGE COLUMNS/LIST/LIST COLUMNS partition.
  • origin_table_name must be a partitioned table, and its partition type must be exactly the same as the subpartitions used for the exchange.
  • After the exchange between the partitioned table and the non-partitioned table, the statistics of the related tables will become invalid and need to be collected again.

Syntax

ALTER TABLE target_partition_table_name
    EXCHANGE PARTITION partition_name
    WITH TABLE origin_table_name
    WITHOUT VALIDATION;

Parameters

Usage instructions

The current partition exchange feature enables data exchange between a partition of a partitioned table and a non-partitioned table. It is primarily used as a temporary replacement for the incremental direct load feature. Typically, the characteristics of the partitioned table and the non-partitioned table are as follows:

• Partitioned table: A table that contains historical data, typically with a large volume of data.
• Non-partitioned table: A table that contains incremental data.

The partition exchange feature must be used with extreme caution because improper operation can cause data to no longer meet the conditions of the original table's partitioning key, thereby triggering cascading errors. Therefore, when performing partition exchange, follow these steps to determine whether a partitioned table and a non-partitioned table can undergo partition exchange.

1. Use the SHOW CREATE TABLE statement to query the partitioning key and target partition information of the partitioned table. Query the data of the non-partitioned table to determine whether its data meets the data range requirements of the target partition's partitioning key.

   Here, table_name is the name of the table to query.

   SHOW CREATE TABLE table_name;
   

2. Based on the query results, ensure that both the partitioned table and the non-partitioned table comply with the above [Limitations and considerations](#Limitations and considerations).

3. Before performing partition exchange, ensure at least the following conditions are met.

   Attribute	Requirement
   Are they in the same tenant?	Yes
   Are they in the same database?	No, compatible with MySQL, supports cross-database exchange
   Are they in the same TableGroup?	Yes
   Are they in the same TableSpace?	Yes
   Are the character set formats consistent?	Yes
   Are the table types consistent?	Yes, both must be user tables, i.e., USER_TABLE
   Are the number of primary keys consistent?	Yes
   Are the row storage formats consistent?	Yes
   Are the column formats and relative column orders consistent?	Yes
   Are they both auto-increment columns?	Yes
   Are the column default values and generation expressions consistent?	Yes
   Are the encryption methods consistent?	Yes
   Are the columnar storage formats consistent?	Yes
   Are the index formats, statuses, and counts consistent?	Yes, the local indexes of the partitioned table must correspond one-to-one with the indexes of the non-partitioned table
   Are the index table names consistent?	Yes, compatible with MySQL, index definitions with the same name must correspond one-to-one
   Are the constraint formats and included columns consistent?	Yes

4. Use the syntax for partition exchange to perform the exchange. After the exchange, the data of the specified partition of the partitioned table will be migrated to the non-partitioned table, and the data of the non-partitioned table will be migrated to the specified partition of the partitioned table. The global index status of the partitioned table will become Unusable.

Examples

Example 1: Exchange data between a RANGE-partitioned table and a non-partitioned table

• Exchange data between a RANGE-partitioned table and a non-partitioned table.

  1. Create a RANGE-partitioned table named tbl1_r.

     obclient> CREATE TABLE tbl1_r (col1 INT PRIMARY KEY, col2 VARCHAR(50))
         PARTITION BY RANGE(col1) 
             (PARTITION p0 VALUES LESS THAN(10),
             PARTITION p1 VALUES LESS THAN(20),
             PARTITION p2 VALUES LESS THAN(30)
             );
     

  2. Create a non-partitioned table named tbl1.

     obclient> CREATE TABLE tbl1 (col1 INT PRIMARY KEY, col2 VARCHAR(50));
     

  3. Insert data into the tbl1 table.

     obclient> INSERT INTO tbl1 VALUES(1, 'a1'),(2, 'a2');
     

     The return result is as follows:

     Query OK, 2 rows affected
     Records: 2  Duplicates: 0  Warnings: 0
     

  4. Perform a partition exchange between partition p0 of the partitioned table tbl1_r and the non-partitioned table tbl1.

     obclient> ALTER TABLE tbl1_r
         EXCHANGE PARTITION p0
         WITH TABLE tbl1
         WITHOUT VALIDATION;
     

  5. View the data in partition p0 of the partitioned table tbl1_r.

     obclient> SELECT * FROM tbl1_r PARTITION(p0);
     

     The return result is as follows:

     +------+------+
     | col1 | col2 |
     +------+------+
     |    1 | a1   |
     |    2 | a2   |
     +------+------+
     2 rows in set
     

• Exchange data between a LIST-partitioned table and a non-partitioned table.

  1. Create a LIST-partitioned table named tbl1_l.

     obclient> CREATE TABLE tbl1_l (col1 INT, col2 VARCHAR(50), col3 INT)
         PARTITION BY LIST(col3) 
             (PARTITION p0 VALUES IN (1, 2, 3, 4),
             PARTITION p1 VALUES IN (5, 6, 7, 8, 9),
             PARTITION p2 VALUES IN (DEFAULT)
             );
     

  2. Create a non-partitioned table named tbl1_2.

     obclient> CREATE TABLE tbl1_2 (col1 INT, col2 VARCHAR(50), col3 INT);
     

  3. Insert data into the tbl1_2 table.

     obclient> INSERT INTO tbl1_2 VALUES(1, 'a1', 1),
         (2, 'a2', 2),
         (3, 'a3', 3),
         (4, 'a4', 4),
         (5, 'a5', 5);
     

     The return result is as follows:

     Query OK, 5 rows affected
     Records: 5  Duplicates: 0  Warnings: 0
     

  4. Perform a partition exchange between partition p0 of the partitioned table tbl1_l and the non-partitioned table tbl1_2.

     obclient> ALTER TABLE tbl1_l
         EXCHANGE PARTITION p0
         WITH TABLE tbl1_2
         WITHOUT VALIDATION;
     

  5. Query the data in partition p0 of the partitioned table tbl1_l.

     obclient> SELECT * FROM tbl1_l PARTITION(p0);
     

     The expected output is as follows:

     +------+------+------+
     | col1 | col2 | col3 |
     +------+------+------+
     |    1 | a1   |    1 |
     |    2 | a2   |    2 |
     |    3 | a3   |    3 |
     |    4 | a4   |    4 |
     |    5 | a5   |    5 |
     +------+------+------+
     5 rows in set
     

Example 2: Exchange data between a partition of a subpartitioned table and a partition of a partitioned table

1. Create a RANGE+HASH subpartitioned table named tbl2_rh.

   obclient> CREATE TABLE tbl2_rh (col1 INT PRIMARY KEY, col2 INT)
       PARTITION BY RANGE(col1)
       SUBPARTITION BY HASH(col1) SUBPARTITIONS 5
           (PARTITION p0 VALUES LESS THAN (10),
           PARTITION p1 VALUES LESS THAN (20),
           PARTITION p2 VALUES LESS THAN (30),
           PARTITION p3 VALUES LESS THAN (MAXVALUE)
           );
   

2. Create a HASH partitioned table named tbl2_h.

   obclient> CREATE TABLE tbl2_h (col1 INT PRIMARY KEY, col2 INT)
       PARTITION BY HASH(col1) PARTITIONS 5;
   

3. Insert data into the tbl2_rh table.

   obclient> INSERT INTO tbl2_rh VALUES(11, 30), (14, 40), (26, 150), (29, 160);
   

   The expected output is as follows:

   Query OK, 4 rows affected
   Records: 4  Duplicates: 0  Warnings: 0
   

4. Query the data in partition p1 of the partitioned table tbl2_rh.

   obclient> SELECT * FROM tbl2_rh PARTITION(p1);
   

   The expected output is as follows:

   +------+------+
   | col1 | col2 |
   +------+------+
   |   11 |   30 |
   |   14 |   40 |
   +------+------+
   2 rows in set
   

5. Insert data into the tbl2_h table.

   obclient> INSERT INTO tbl2_h VALUES(12, 20), (16, 110), (17, 170), (19, 120);
   

   The expected output is as follows:

   Query OK, 4 rows affected
   Records: 4  Duplicates: 0  Warnings: 0
   

6. Perform a partition exchange between partition p1 of the subpartitioned table tbl2_rh and the partitioned table tbl2_h.

   obclient> ALTER TABLE tbl2_rh
       EXCHANGE PARTITION p1
       WITH TABLE tbl2_h
       WITHOUT VALIDATION;
   

7. View the data in partition p1 of the partitioned table tbl2_rh.

   obclient> SELECT * FROM tbl2_rh PARTITION(p1);
   

   The result is as follows:

   +------+------+
   | col1 | col2 |
   +------+------+
   |   16 |  110 |
   |   12 |   20 |
   |   17 |  170 |
   |   19 |  120 |
   +------+------+
   4 rows in set
   

Example 3: Exchange data between a subpartition of a subpartitioned table and a non-partitioned table

1. Create a RANGE+RANGE subpartitioned table named tbl3_rr.

   obclient> CREATE TABLE tbl3_rr(col1 INT, col2 INT)
       PARTITION BY RANGE(col1)
       SUBPARTITION BY RANGE(col2)
           (PARTITION p0 VALUES LESS THAN(10)
           (SUBPARTITION sp0 VALUES LESS THAN(20),
           SUBPARTITION sp1 VALUES LESS THAN(50),
           SUBPARTITION sp2 VALUES LESS THAN (MAXVALUE)
           ),
           PARTITION p1 VALUES LESS THAN(20)
           (SUBPARTITION sp3 VALUES LESS THAN(20),
           SUBPARTITION sp4 VALUES LESS THAN(50),
           SUBPARTITION sp5 VALUES LESS THAN (MAXVALUE)
           )
           );
   

2. Create a non-partitioned table named tbl3.

   obclient> CREATE TABLE tbl3 (col1 INT, col2 INT);
   

3. Insert data into the tbl3_rr table.

   obclient> INSERT INTO tbl3_rr VALUES(1, 10), (4, 15), (6, 12), (19, 160);
   

   The result is as follows:

   Query OK, 4 rows affected
   Records: 4  Duplicates: 0  Warnings: 0
   

4. View the data in partition sp0 of the partitioned table tbl3_rr.

   obclient> SELECT * FROM tbl3_rr PARTITION(sp0);
   

   The result is as follows:

   +------+------+
   | col1 | col2 |
   +------+------+
   |    1 |   10 |
   |    4 |   15 |
   |    6 |   12 |
   +------+------+
   3 rows in set
   

5. Insert data into the tbl3 table.

   obclient> INSERT INTO tbl3 VALUES(2, 20), (3, 10), (5, 17), (8, 12);
   

   The result is as follows:

   Query OK, 4 rows affected
   Records: 4  Duplicates: 0  Warnings: 0
   

6. Perform a partition exchange between partition sp0 of the subpartitioned table tbl3_rr and the non-partitioned table tbl3.

   obclient> ALTER TABLE tbl3_rr
       EXCHANGE PARTITION sp0
       WITH TABLE tbl3
       WITHOUT VALIDATION;
   

7. View the data in partition sp0 of the partitioned table tbl3_rr.

   obclient> SELECT * FROM tbl3_rr PARTITION(sp0);
   

   The return result is as follows:

   +------+------+
   | col1 | col2 |
   +------+------+
   |    2 |   20 |
   |    3 |   10 |
   |    5 |   17 |
   |    8 |   12 |
   +------+------+
   4 rows in set
   

References

• ALTER TABLE
• SELECT