TRUNCATE 或 DROP 表分区后导致全局索引失效

本文介绍 TRUNCATE 或 DROP 表分区后导致全局索引失效的解决方法。

适用版本

OceanBase 数据库所有版本。

问题现象

在分区表上创建了全局索引的情况下，TRUNCATE 或 DROP 表分区后导致全局索引失效，问题复现的方式如下。

1. 创建测试表。

   obclient>CREATE TABLE  LOC_IM_MID_PRES_ADDR_TEST
       (
         HOUSEID        NUMBER(16),
         HOUSENM        VARCHAR2(128),
         CITYNM         VARCHAR2(128),
         CITYID         NUMBER(16),
         COUNTYNM       VARCHAR2(128),
         COUNTYID       NUMBER(16),
         TOWNNM         VARCHAR2(128),
         TOWNID         NUMBER(16),
         ROADNM         VARCHAR2(128),
         ROADID         NUMBER(16),
         ZONENM         VARCHAR2(128),
         ZONEID         NUMBER(16),
         BUILDINGNM     VARCHAR2(128),
         BUILDINGID     NUMBER(16),
         UNITNM         VARCHAR2(128),
         UNITID         NUMBER(16),
         FLOORNM        VARCHAR2(128),
         FLOORID        NUMBER(16),
         FULLADDR       VARCHAR2(1024),
         COVERTYPE      VARCHAR2(32),
         PYCAPLOWER     VARCHAR2(256),
         FULLPYLOWER    VARCHAR2(256),
         PORTS          VARCHAR2(16),
         ADDR_TYPE      VARCHAR2(32),
         COVER_SCENARIO VARCHAR2(32)
       )
       PARTITION BY RANGE (CITYID)
       (
         partition P_530 VALUES LESS THAN (531),
         partition P_531 VALUES LESS THAN (532),
         partition P_532 VALUES LESS THAN (533),
         partition P_533 VALUES LESS THAN (534),
         partition P_534 VALUES LESS THAN (535),
         partition P_535 VALUES LESS THAN (536),
         partition P_536 VALUES LESS THAN (537),
         partition P_537 VALUES LESS THAN (538),
         partition P_538 VALUES LESS THAN (539),
         partition P_539 VALUES LESS THAN (540),
         partition P_543 VALUES LESS THAN (544),
         partition P_546 VALUES LESS THAN (547),
         partition P_631 VALUES LESS THAN (632),
         partition P_632 VALUES LESS THAN (633),
         partition P_633 VALUES LESS THAN (634),
         partition P_634 VALUES LESS THAN (635),
         partition P_635 VALUES LESS THAN (636),
         partition P_999 VALUES LESS THAN (1000)
       );
   

2. 在测试表上创建全局索引，并检查全局索引是否生效。

   此时全局索引是生效的。

   obclient> CREATE index idx_house_id ON LOC_IM_MID_PRES_ADDR_TEST(HOUSEID) GLOBAL;
   obclient> INSERT INTO LOC_IM_MID_PRES_ADDR_TEST SELECT * FROM LOC_IM_MID_PRES_ADDR WHERE CITYID=530 AND rownum < 10000;
   Query OK, 9999 rows affected (0.38 sec)
   
   obclient> commit;
   
   obclient> SELECT a.table_name,b.index_name,b.status
   FROM dba_tables a, dba_indexes b
   WHERE a.OWNER = 'IM'
   AND a.PARTITIONED = 'YES'
   AND a.OWNER = b.table_owner
   AND a.TABLE_NAME = b.table_name
   AND b.partitioned = 'NO';
   
   +---------------------------+------------------------------+----------+
   | TABLE_NAME                | INDEX_NAME                   | STATUS   |
   +---------------------------+------------------------------+----------+
   | LOC_IM_MID_PRES_ADDR_TEST | IDX_HOUSE_ID                 | VALID    |
   +---------------------------+------------------------------+----------+
   

3. DROP 或 TRUNCATE 某一分区，检查全局索引是否生效。

   此时索引状态为 UNUSABLE，表示不可用。

   obclient> ALTER TABLE LOC_IM_MID_PRES_ADDR_TEST truncate partition P_530;
   obclient> SELECT a.table_name,b.index_name,b.status
   FROM dba_tables a, dba_indexes b
   WHERE a.OWNER = 'IM'
   AND a.PARTITIONED = 'YES'
   AND a.OWNER = b.table_owner
   AND a.TABLE_NAME = b.table_name
   AND b.partitioned = 'NO';
   
   +---------------------------+------------------------------+----------+
   | TABLE_NAME                | INDEX_NAME                   | STATUS   |
   +---------------------------+------------------------------+----------+
   | LOC_IM_MID_PRES_ADDR_TEST | IDX_HOUSE_ID                 | UNUSABLE |
   +---------------------------+------------------------------+----------+
   

规避方式

将 OceanBase 数据库升级到 V2.2.76 BP1 及以上版本。有关升级 OceanBase 数据库的方式，请参见《OceanBase 数据库 升级指南》。

OceanBase 数据库 V2.2.76 BP1 及以上版本在 TRUNCATE 或 DROP 分区后，通过 UPDATE GLOBAL INDEXES 子句会自动维护失效的全局索引。

以下为在 OceanBase 数据库 V2.2.76 BP1 版本的测试。

obclient> ALTER TABLE LOC_IM_MID_PRES_ADDR_TEST DROP partition P_530 UPDATE GLOBALINDEXES;
Query OK, 0 rows affected (5.64 sec)

obclient> SELECT a.table_name,b.index_name,b.status
FROM dba_tables a, dba_indexes b
WHERE a.OWNER = 'IM'
AND a.PARTITIONED = 'YES'
AND a.OWNER = b.table_owner
AND a.TABLE_NAME = b.table_name
AND b.partitioned = 'NO';
+---------------------------+------------------------------+----------+
| TABLE_NAME                | INDEX_NAME                   | STATUS   |
+---------------------------+------------------------------+----------+
| LOC_IM_MID_PRES_ADDR_TEST | IDX_HOUSE_ID                 | VALID    |
+---------------------------+------------------------------+----------+