Partition exchange

The partition exchange feature in OceanBase Database is primarily used to enable fast data loading into partitioned tables. This feature allows you to quickly import data into a newly created non-partitioned table using a bulk direct load method, and then efficiently move that data into a partitioned table through partition exchange. By doing so, it significantly enhances the performance of incremental data imports into existing partitioned tables.

Partition exchange can also be considered as an alternative approach for incremental direct loads. For example, to import incremental data into partitioned table A using the partition exchange feature, you can follow these steps:

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

Support for partition exchange

The following table describes the support for partition exchange. The column indicates the target table (target_partition_table_name), and the row indicates the source table (origin_table_name).

Limitations and considerations

• Data validation is not supported, so you must specify WITHOUT VALIDATION. It is up to the user to ensure the validity of the exchanged data.

• The table-level and column-level properties of the partitioned and non-partitioned tables must match exactly.

• Both tables must have identical constraints, and the current version does not support foreign key constraints between them.

• Local indexes in the partitioned table must correspond one-to-one with the indexes in the non-partitioned table.

• Only the INCLUDING INDEXES option is supported (in MySQL-compatible mode, this is the default behavior). Local indexes associated with the partition's data will be included in the exchange, and their usability status will remain unchanged after the swap.

• UPDATE GLOBAL INDEXES is not supported, so after a successful exchange, all global indexes will be in an Unusable state.

• Currently, the partition exchange feature is recommended only as a temporary solution for incremental direct loads. For details on incremental direct loads, refer to Direct load overview.

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

  • The partition must be of type Range, Range Columns, or List.

    Note

    For exchanges between partitioned tables and non-partitioned tables, starting from V4.3.5 BP5, list partitions are supported in V4.3.5.

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

  • There are no restrictions on the partition type, but the subpartition must be of type range or range columns.

• When exchanging data between the partition of a subpartitioned table and a partitioned table:

  • target_partition_table_name must be a subpartitioned table, and the partition specified for the exchange must be a partition of that table. The partition in the subpartitioned table must be of type Range, Range Columns, List, or List Columns.
  • origin_table_name must be a partitioned table, and its partition type must match exactly with the subpartition type under the partition being exchanged.
  • After exchanging between partitioned and subpartitioned tables, the relevant table statistics will become invalid and need to be recalculated.

Syntax

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

Parameters

Considerations

The partition exchange feature allows you to exchange data between a partition of a partitioned table and a non-partitioned table. This feature is primarily used as a temporary replacement for incremental direct load. Here are the typical characteristics of a partitioned table and a non-partitioned table:

• Partitioned table: A table that contains historical data and typically has a large amount of data.
• Non-partitioned table: A table that contains incremental data.

The partition exchange feature must be used with caution, as improper operations can result in data that does not meet the partitioning key conditions of the original table, leading to a chain of errors. Therefore, before performing a partition exchange, you must follow these steps to determine whether the partitioned table and the non-partitioned table can be exchanged.

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 the data of the non-partitioned table meets the data range requirements of the target partition.

   Here, table_name is the name of the table to be queried.

   SHOW CREATE TABLE table_name;
   

2. Based on the query results, ensure that the partitioned table and the non-partitioned table meet the requirements in the Limitations and considerations section.

3. Before performing a partition exchange, ensure that the following conditions are met:

   Attribute	Requirement
   Whether they are in the same tenant	Yes
   Whether they are in the same database	No. It is compatible with MySQL and supports cross-database exchange.
   Whether they are in the same table group (TableGroup)	Yes
   Whether they are in the same table space (TableSpace)	Yes
   Whether the character set format is consistent	Yes
   Whether the table types are consistent	Yes. They must both be user tables, i.e., USER_TABLE.
   Whether the number of primary keys is consistent	Yes
   Whether the row storage format is consistent	Yes
   Whether the column format and the relative order of columns are consistent	Yes
   Whether they are all auto-increment columns	Yes
   Whether the default values and generation list expressions of columns are consistent	Yes
   Whether the encryption method is consistent	Yes
   Whether the column storage format is consistent	Yes
   Whether the index format, status, and quantity are consistent	Yes. The local indexes of the partitioned table must correspond one-to-one with the indexes of the non-partitioned table.
   Whether the index table names are consistent	Yes. It is compatible with MySQL, and the index definitions with the same table name correspond one-to-one.
   Whether the constraint format and included columns are consistent	Yes

4. Use the partition exchange syntax to perform the exchange. After the exchange, the data in the specified partition of the partitioned table will be migrated to the non-partitioned table, and the data in the non-partitioned table will be migrated to the specified partition of the partitioned table. The global index of the partitioned table will be marked as 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. Exchange the partition p0 of the partitioned table tbl1_r with the non-partitioned table tbl1.

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

  5. Query the data in the 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. Exchange the partition p0 of the partitioned table tbl1_l with 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 the partition p0 of the partitioned table tbl1_l.

     obclient> SELECT * FROM tbl1_l PARTITION(p0);
     

     The return result 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 partitioned table

1. Create a subpartitioned table tbl2_rh with Range partitions and Hash subpartitions.

   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 partitioned table tbl2_h with Hash partitions.

   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 return result is as follows:

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

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

   obclient> SELECT * FROM tbl2_rh PARTITION(p1);
   

   The return result 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 return result is as follows:

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

6. Exchange the data in partition p1 of the tbl2_rh table with the data in the tbl2_h table.

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

7. Query the data in partition p1 of the tbl2_rh table.

   obclient> SELECT * FROM tbl2_rh PARTITION(p1);
   

   The return 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 partitioned table and a non-partitioned table

1. Create a range + range partitioned 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 return result is as follows:

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

4. Query the data in the sp0 subpartition of the tbl3_rr table.

   obclient> SELECT * FROM tbl3_rr PARTITION(sp0);
   

   The return 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 return result is as follows:

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

6. Exchange the sp0 subpartition of the tbl3_rr table with the tbl3 table.

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

7. Query the data in the sp0 subpartition of the tbl3_rr table.

   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