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 new non-partitioned table, and then using the partition exchange feature to quickly transfer the data from the non-partitioned table into the partitioned table, thereby improving the performance of incremental data import for existing partitioned tables.

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

1. Create an empty new partition P in 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 exchange the new partition P in table A with table B.

Supported partition exchange scenarios

The following table describes the supported scenarios of partition exchange. The "Type" column indicates the target table (target_partition_table_name), and the row of type 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 non-partitioned table must be the same.

• The constraints of the partitioned table and non-partitioned table must be the same. In the current version, foreign key constraints are not supported between the two tables.

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

• Only the INCLUDING INDEXES option is supported (in Oracle-compatible mode, you must explicitly specify this option). When you enable the INCLUDING INDEXES option, the local index that corresponds to the data in a partition is exchanged with the index of the non-partitioned table. After the exchange, the local index remains available.

• UPDATE GLOBAL INDEXES is not supported. After the exchange, all global indexes will be in the unusable state.

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

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

  • The partitioned table must be partitioned by range or by list.

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

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

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

  • target_partition_table_name must be a subpartitioned table, and the partition to be exchanged must be a partition of this table. In addition, the partition must be partitioned by range or by list.
  • origin_table_name must be a partitioned table. The partition type of this table must be the same as the partition type of the subpartition of the subpartitioned table.
  • After the exchange, the statistics of the involved tables will be invalid and need to 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;

Parameter description

Considerations

The current partition exchange feature is used to exchange data between a partition of a partitioned table and a non-partitioned table. It is mainly used as a temporary replacement for incremental direct load. The following table describes the characteristics of a partitioned table and a non-partitioned table.

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

You must be extremely careful when you use the partition exchange feature. Improper operations may cause 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, you must check whether the partitioned table and non-partitioned table meet the following requirements.

1. Run 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.

   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 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 the same	Yes
   Whether the table types are the same	Yes. The tables must be user tables, that is, USER_TABLE.
   Whether the number of primary keys is the same	Yes
   Whether the row storage formats are the same	Yes
   Whether the column formats and the relative column order are the same	Yes
   Whether all columns are auto-increment columns	Yes
   Whether the default values and generation expressions of the columns are the same	Yes
   Whether the encryption methods are the same	Yes
   Whether the column storage formats are the same	Yes
   Whether the index formats, states, and numbers are the same	Yes. The local indexes of the partitioned table must be the same as the indexes of the non-partitioned table.
   Whether the index table names are the same	No
   Whether the constraint formats and included columns are the same	Yes

4. Run the partition exchange statement. 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 will be 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 p0 partition 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 p0 partition 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 p0 partition 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 p0 partition 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 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 return result is as follows:

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

4. Query the data in the p1 partition 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 p1 partition of the subpartitioned 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 p1 partition 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 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, 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 sp1 subpartition 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 sp1 subpartition 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 sp1 subpartition 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