Partition exchange

The partition exchange feature of OceanBase Database is mainly used to quickly import data into a partitioned table for efficient data loading. This involves using a full direct load to import data into a newly created non-partitioned table and then using the partition exchange feature to quickly move the data from the non-partitioned table into the partitioned table, thereby improving the performance of incremental data imports into existing partitioned tables.

The partition exchange feature can also be considered as an alternative to 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 non-partitioned table B with the same structure as table A and use direct load to import the incremental data into table B.
3. Use the partition exchange feature to swap the new partition P of table A with table B.

Supported partition exchanges

The following table describes the supported operations for partition exchanges. The type column represents the target partitioned table (target_partition_table_name), and the type row represents the source table (origin_table_name).

Limitations and notes

• Data validation is not supported. That is, you must specify WITHOUT VALIDATION. You are advised to ensure that the exchanged data is effective.

• At the table level and column level, the partitioned tables must match the corresponding non-partitioned tables in the following attributes: table name, schema, column list, column data type, and default value.

• At the table level and column level, the partitioned tables and non-partitioned tables must have the same constraints. Moreover, in the current version, foreign key constraints are not supported between these two types of tables.

• At the table level and column level, the local indexes of the partitioned tables and the indexes of the non-partitioned tables must be identical.

• In MySQL mode, the indexes of the source and target tables must have identical definitions (index name, type, columns, and column order). However, OceanBase Database cannot detect the following discrepancies:

  • The prefix lengths of prefix indexes are different.
  • The expressions of virtual generated columns are inconsistent.

  Note

  If the indexes of the source and target tables are different, the partition exchange operation is rejected. For the situations listed in the preceding paragraph that cannot be identified by OceanBase Database, you are advised to ensure that the indexes of the source and target tables are identical to avoid potential errors during data exchange or query execution.

• Only the behavior of INCLUDING INDEXES is supported. This behavior is the default behavior in MySQL mode. The data of local indexes corresponding to the exchanged partitions will also be exchanged. After the exchange, the local indexes remain available.

• UPDATE GLOBAL INDEXES is not supported. Therefore, all global indexes are unavailable (Unusable) after the exchange.

• The current partition exchange feature is only recommended for use as a temporary solution during incremental direct load operations. For more information about incremental direct load, see Overview.

• When you exchange partitions between a partitioned table and a non-partitioned table, the partitioned table must be partitioned by RANGE, RANGE COLUMNS, or LIST, and the non-partitioned table must be the target table.

  • When you exchange partitions between a subpartitioned table and a non-partitioned table, the subpartitioned table must be partitioned by RANGE or RANGE COLUMNS, but the partitioned table type is not specified.

• When you exchange partitions between a subpartitioned table and a partitioned table, the following conditions must be met:

  • The target_partition_table_name must be a subpartitioned table, and the specified partition must be the name of a partition of the subpartitioned table. Moreover, the subpartitioned table must be partitioned by RANGE, RANGE COLUMNS, LIST, or LIST COLUMNS.

• The origin_table_name must be a partitioned table, and the partition type must be identical to the partition type specified for the subpartitioned table.

  • After the exchange, statistics for the tables are invalidated 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

Considerations

The partition exchange feature allows you to exchange data between a partition of a partitioned table and a non-partitioned table. It is provided as a temporary substitution for the incremental direct load feature. The general characteristics of partitioned and non-partitioned tables are as follows:

• A partitioned table contains historical data and usually has a large data size.

• A non-partitioned table contains incremental data.

An improper operation during partition exchange may produce data that does not meet the conditions specified by the partitioning key of the original table and further trigger other errors. Therefore, proceed with caution when you use this feature. Before you exchange data between a partitioned table and a non-partitioned table, perform the following steps to determine whether the tables meet the partition exchange conditions:

1. Execute the SHOW CREATE TABLE statement to query information about the partitioning key and target partition of the partitioned table. Query data of the non-partitioned table and determine whether the data falls within the data range specified by the partitioning key of the target partition.

   SHOW CREATE TABLE table_name;
   

   In the statement above, table_name specifies the name of the table to be queried.

2. Check the query result to make sure that the partitioned and non-partitioned tables comply with the Limitations and notes.

3. Before you perform partition exchange, make sure that the following conditions are met.

   Attribute	Requirement
   Whether they are in the same tenant	Yes
   Whether they are in the same database	No, compatible with MySQL. 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 sets are consistent	Yes
   Whether the table types are consistent	Yes. Both must be user tables, that is, USER_TABLE.
   Whether the primary key counts are consistent	Yes
   Whether the row storage formats are consistent	Yes
   Whether the column formats and relative column orders are consistent	Yes
   Whether the columns are auto-increment columns	Yes
   Whether the default values and generation expressions of the columns are consistent	Yes
   Whether the encryption methods are consistent	Yes
   Whether the columnar storage formats are consistent	Yes
   Whether the index formats, states, and counts are consistent	Yes. The local indexes of the partitioned table must correspond to the indexes of the non-partitioned table.
   Whether the index table names are consistent	Yes, compatible with MySQL. The index definitions of tables with the same name must correspond to each other.
   Whether the constraint formats and included columns are consistent	Yes

4. Execute the partition exchange statement to exchange data between the specified partition of the partitioned table and the non-partitioned table. After the partition exchange is successful, the global index of the partitioned table is in the unusable state.

Examples

Example 1: Exchange data between a 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 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 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. View 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 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 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. View 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 composite partitioned table and a partitioned table

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

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

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

   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 partition p1 of the composite partitioned table tbl2_rh with the partitioned table tbl2_h.

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

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

   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 partition of a composite partitioned table and a non-partitioned table

1. Create a RANGE + RANGE composite partitioned table 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 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. View the data in the partition sp0 of the partitioned table tbl3_rr.

   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 partition sp0 of the composite partitioned table tbl3_rr with the non-partitioned table tbl3.

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

7. View the data in the 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