truncate/drop partition 时 table_id 不发生变化

OceanBase 数据库 V4.x 版本 MySQL 模式下所支持的 Offline DDL 操作的列表中包含分区操作的删除分区和 TRUNCATE 分区。根据离线 DDL 的原理，可以通过 DDL 操作完成后 table_id 是否会发生变化来判断是否为离线 DDL。但是在实际的测试过程中发现，删除分区和 truncate 分区时 table_id 未发生变化。

具体测试如下。

1. 创建测试表。

   CREATE TABLE test (
       id INT NOT NULL AUTO_INCREMENT,
       event_time DATETIME NOT NULL,
       message TEXT,
       PRIMARY KEY (id, event_time)
   )
   PARTITION BY RANGE (TO_DAYS(event_time)) (
       PARTITION p20231001 VALUES LESS THAN (TO_DAYS('2023-10-02')),
       PARTITION p20231002 VALUES LESS THAN (TO_DAYS('2023-10-03')),
       PARTITION p20231003 VALUES LESS THAN (TO_DAYS('2023-10-04'))
   );
   
   INSERT INTO test (event_time, message) VALUES
   ('2023-10-01 08:00:00', 'First event of October 1st'),
   ('2023-10-02 12:30:00', 'Second event of October 2nd'),
   ('2023-10-03 15:45:00', 'Third event of October 3rd'); 
   
   
   SELECT 'p20231001' AS partition_name, * FROM test PARTITION (p20231001);
   SELECT 'p20231002' AS partition_name, * FROM test PARTITION (p20231002);
   SELECT 'p20231003' AS partition_name, * FROM test PARTITION (p20231003);
   

2. 观察 truncate paritition 前后 table_id 不变

   obclient(root@test1)[oceanbase]> select database_name,table_name,partition_name,subpartition_name,
   table_id,tablet_id,object_id from dba_ob_table_locations where table_name='test' and role='leader';
   +---------------+---------------+----------------+-------------------+----------+-----------+-----------+
   | database_name | table_name    | partition_name | subpartition_name | table_id | tablet_id | object_id |
   +---------------+---------------+----------------+-------------------+----------+-----------+-----------+
   | test          | test          | p20231001      | NULL              |   550570 |    208025 |    550573 |
   | test          | test          | p20231002      | NULL              |   550570 |    208026 |    550574 |
   | test          | test          | p20231003      | NULL              |   550570 |    208027 |    550575 |
   +---------------+---------------+----------------+-------------------+----------+-----------+-----------+
   3 rows in set (0.476 sec)
   
   obclient(root@test1)[test]> SELECT 'p20231003' AS partition_name, * FROM test PARTITION (p20231003);
   +----------------+------+---------------------+----------------------------+
   | partition_name | id   | event_time          | message                    |
   +----------------+------+---------------------+----------------------------+
   | p20231003      |    3 | 2023-10-03 15:45:00 | Third event of October 3rd |
   +----------------+------+---------------------+----------------------------+
   1 row in set (0.001 sec)
   
   obclient(root@test1)[test]> alter table test  truncate partition p20231003;
   Query OK, 0 rows affected (0.536 sec)
   
   obclient(root@test1)[test]> SELECT 'p20231003' AS partition_name, * FROM test PARTITION (p20231003);
   Empty set (0.009 sec)
   
   obclient(root@test1)[oceanbase]> select database_name,table_name,partition_name,subpartition_name,
   table_id,tablet_id,object_id from dba_ob_table_locations where table_name='test' and role='leader';
   +---------------+---------------+----------------+-------------------+----------+-----------+-----------+
   | database_name | table_name    | partition_name | subpartition_name | table_id | tablet_id | object_id |
   +---------------+---------------+----------------+-------------------+----------+-----------+-----------+
   | test          | test          | p20231001      | NULL              |   550570 |    208025 |    550573 |
   | test          | test          | p20231002      | NULL              |   550570 |    208026 |    550574 |
   | test          | test          | p20231003      | NULL              |   550570 |    208028 |    550582 |
   +---------------+---------------+----------------+-------------------+----------+-----------+-----------+
   3 rows in set (0.082 sec)
   

3. 观察 drop partition 前后 table_id 不变

   obclient(root@test1)[test]> alter table test drop partition p20231002;
   Query OK, 0 rows affected (0.409 sec)
   
   obclient(root@test1)[oceanbase]> select database_name,table_name,partition_name,subpartition_name,
   table_id,tablet_id,object_id from dba_ob_table_locations where table_name='test' and role='leader';
   +---------------+---------------+----------------+-------------------+----------+-----------+-----------+
   | database_name | table_name    | partition_name | subpartition_name | table_id | tablet_id | object_id |
   +---------------+---------------+----------------+-------------------+----------+-----------+-----------+
   | test          | test          | p20231001      | NULL              |   550570 |    208025 |    550573 |
   | test          | test          | p20231003      | NULL              |   550570 |    208028 |    550582 |
   +---------------+---------------+----------------+-------------------+----------+-----------+-----------+
   2 rows in set (0.069 sec)
   

问题原因

对于删除分区和 truncate 分区操作，为了优化 truncate 的性能，OceanBase 数据库实现了执行操作后 table_id 不变化，只修改 tablet_id 和 object_id。

适用版本

OceanBase 数据库 V4.x 版本。