Exchange partitions

The partition exchange feature of OceanBase Database allows you to quickly import data into partitioned tables for efficient data loading. To be specific, this feature quickly inserts data into a new non-partitioned table through full direct load and then imports data from the non-partitioned table to an existing partitioned table to speed up incremental data import for the partitioned table.

Limitations

• This feature supports only RANGE partitioning.

  • If you want to exchange data of a partition, the partitioning type must be RANGE partitioning.
  • If you want to exchange data of a subpartition of a subpartitioned table, the subpartitioning type must be RANGE partitioning, but the partitioning type is not limited.

• This feature does not support data validation. You must specify the WITHOUT VALIDATION clause and manually verify the validity of exchanged data.

• Table and column attributes of the partitioned table must be consistent with those of the non-partitioned table.

• Constraints on the partitioned table must be the same as those on the non-partitioned table. OceanBase Database V4.3.1 does not support FOREIGN KEY constraints on either table.

• The local index table of the partitioned table must be consistent with the index table of the non-partitioned table.

• This feature supports only operations with the INCLUDING INDEXES clause. Data of partitions corresponding to local indexes will also be exchanged. The local indexes will remain in the usable state after the partition exchange is successful.

• This feature does not support the UPDATE GLOBAL INDEXES clause. Therefore, global indexes will remain in the unusable state after the partition exchange is successful.

• We recommend that you use this feature only as a temporary substitution for the incremental direct load feature. For more information about the incremental direct load feature, see Overview.

Syntax

ALTER TABLE partition_table_name
    EXCHANGE PARTITION partition_name
    WITH TABLE non_partition_table_name
    INCLUDING INDEXES
    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.

   table_name specifies the name of the table to be queried.

   SHOW CREATE TABLE table_name;
   

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

3. 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

1. Create a partitioned table named tbl1_r.

   CREATE TABLE tbl1_r (col1 INT PRIMARY KEY, col2 VARCHAR2(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.

   CREATE TABLE tbl1 (col1 INT PRIMARY KEY, col2 VARCHAR2(50));
   

3. Insert data into the tbl1 table.

   INSERT INTO tbl1 VALUES(1, 'a1'),(2, 'a2'),(15, 'a15');
   

   The return result is as follows:

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

4. Exchange data between the p0 partition of the tbl1_r table and the tbl1 table.

   ALTER TABLE tbl1_r
       EXCHANGE PARTITION p0
       WITH TABLE tbl1
       INCLUDING INDEXES
       WITHOUT VALIDATION;
   

5. Query data in the p0 partition of the tbl1_r table.

   SELECT * FROM tbl1_r PARTITION(p0);
   

   The return result is as follows:

   +------+------+
   | COL1 | COL2 |
   +------+------+
   |    1 | a1   |
   |    2 | a2   |
   |   15 | a15  |
   +------+------+
   3 rows in set
   

References

• ALTER TABLE
• SELECT statement