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 is achieved by first using a full direct load to import data into a newly created non-partitioned table, and then using the partition exchange feature to quickly import the data from the non-partitioned table into the partitioned table, thereby improving the performance of incremental data import for existing partitioned tables.

It is recommended to use the partition exchange feature 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 a direct load to import the incremental data into table B.
3. Use the partition exchange feature to exchange the new partition P of table A with table B.

Support for partition exchange

The following table describes the support for partition exchange, where 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. You must specify WITHOUT VALIDATION. You need to ensure the validity of the exchanged data.

• The table-level and column-level attributes of the partitioned table and the non-partitioned table must be consistent.

• The constraints of the partitioned table and the non-partitioned table must be consistent. However, the current version does not support foreign key constraints between the two tables.

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

• Only the INCLUDING INDEXES option is supported (you must explicitly specify INCLUDING INDEXES in Oracle mode). The data of the local index corresponding to the partition will also be exchanged. After the exchange, the local index remains available.

• The UPDATE GLOBAL INDEXES option is not supported. Therefore, all global indexes are in the unusable state after the exchange.

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

• When you exchange data between a partitioned table and a non-partitioned table:

  • The partitioned table must be a range-partitioned or list-partitioned table.

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

  • The partitioned table can be any type, but the subpartition must be a range-partitioned subpartition.

• When you exchange data between a partition of a subpartitioned table and a partitioned table:

  • The target_partition_table_name must be a subpartitioned table, and the specified partition must be the name of one of its partitions. The partition must be a range-partitioned or list-partitioned partition.
  • The origin_table_name must be a partitioned table, and the partition type must be consistent with that of the subpartition of the partitioned table.
  • After the exchange, the statistics of the related tables become invalid and must be collected again.

Syntax

ALTER TABLE target_partition_table_name
    EXCHANGE {PARTITION partition_name
             | SUBPARTITION subpartition_name}
    WITH TABLE origin_table_name
    INCLUDING INDEXES
    WITHOUT VALIDATION;

Parameters

Usage notes

The current partition exchange feature is used to exchange data between a partition of a partitioned table and a non-partitioned table. It is recommended only as a temporary replacement for incremental bypass load. Typically, the partitioned table and the non-partitioned table have the following characteristics:

• A partitioned table contains historical data and has a large amount of data.
• A non-partitioned table contains incremental data.

Use the partition exchange feature with caution. Improper operations may cause the data to not meet the partitioning key conditions of the original table, which may trigger a chain of errors. Therefore, before you perform a partition exchange, check whether the partitioned table and the non-partitioned table meet the following conditions:

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 it meets the data range requirements of the target partition's partitioning key.

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

   SHOW CREATE TABLE table_name;
   

2. Ensure that the partitioned table and the non-partitioned table meet the requirements described in Limitations and considerations.

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

   Attribute	Requirement
   Whether the two tables are in the same tenant	Yes
   Whether the two tables are in the same schema	Yes
   Whether the two tables are in the same table group (TableGroup)	Yes
   Whether the two tables are in the same table space (TableSpace)	Yes
   Whether the character set formats are consistent	Yes
   Whether the table types are consistent	Yes. The table types must be both user tables, that is, USER_TABLE.
   Whether the number of primary keys is consistent	Yes
   Whether the row storage formats are consistent	Yes
   Whether the column formats and the relative order of columns are consistent	Yes
   Whether all columns are auto-increment columns	Yes
   Whether the default values and generation list expressions of columns are consistent	Yes
   Whether the encryption methods are consistent	Yes
   Whether the column storage formats are consistent	Yes
   Whether the index formats, states, and numbers are consistent	Yes. The local indexes of the partitioned table must be consistent with the indexes of the non-partitioned table.
   Whether the index table names are consistent	No
   Whether the constraint formats and included columns are consistent	Yes

4. Perform a partition exchange by using the partition exchange syntax. 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 indexes of the partitioned table are 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 named tbl1_r.

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

     obclient> CREATE TABLE tbl1 (col1 INT PRIMARY KEY, col2 VARCHAR2(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
         INCLUDING INDEXES
         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 (1, 2, 3, 4),
             PARTITION p1 VALUES (5, 6, 7, 8, 9),
             PARTITION p2 VALUES (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
         INCLUDING INDEXES
         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 subpartition of a partitioned table and a partitioned table

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

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

4. Query 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 partitioned table tbl2_rh with the partitioned table tbl2_h.

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

7. Query 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 |
   +------+------+
   |   19 |  120 |
   |   12 |   20 |
   |   16 |  110 |
   |   17 |  170 |
   +------+------+
   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, 30), (4, 40), (16, 150), (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 subpartition sp1 of the partitioned table tbl3_rr.

   obclient> SELECT * FROM tbl3_rr PARTITION(sp1);
   

   The return result is as follows:

   +------+------+
   | COL1 | COL2 |
   +------+------+
   |    1 |   30 |
   |    4 |   40 |
   +------+------+
   2 rows in set
   

5. Insert data into the tbl3 table.

   obclient> INSERT INTO tbl3 VALUES(2, 21), (6, 30), (7, 35), (9, 40);
   

   The return result is as follows:

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

6. Exchange the subpartition sp1 of the partitioned table tbl3_rr with the non-partitioned table tbl3.

   obclient> ALTER TABLE tbl3_rr
       EXCHANGE SUBPARTITION sp1
       WITH TABLE tbl3
       INCLUDING INDEXES
       WITHOUT VALIDATION;
   

7. Query the data in the subpartition sp1 of the partitioned table tbl3_rr.

   obclient> SELECT * FROM tbl3_rr PARTITION(sp1);
   

   The return result is as follows:

   +------+------+
   | COL1 | COL2 |
   +------+------+
   |    2 |   21 |
   |    6 |   30 |
   |    7 |   35 |
   |    9 |   40 |
   +------+------+
   4 rows in set
   

References

• ALTER TABLE
• SELECT statement