划线反馈

在 OceanBase 数据库中删除分区表的全局唯一索引时，是否会触发索引重建？

更新时间：2024-05-20 01:21

适用版本：V2.1.x、V2.2.x、V3.1.x、V3.2.x内容类型：FAQ

会触发重建。

重建过程中全局唯一索引会失效，这个期间可能会写入违反唯一性约束的数据，导致全局唯一索引失效，需要特别注意。

当出现上述问题时，可以按照如下方式尝试解决。

在 truncate/drop 分区时加上 global indexs 防止全局分区索引失效。
临时应急: 需要手动清除冲突数据后，手动删除全局唯一索引，然后手动重新创建全局唯一索引。drop 掉全局唯一索引并重新重建。

适用版本

OceanBase 数据库 V2.x 和 V3.x 版本。

示例

示例如下。

创建表 part01。

obclient > create table part01
(prod_id number,
cust_id number,
time_id date,
quantity_sold number(3))
partition by range(time_id)
(partition part_q1_2023 values less than(to_date('1-4-2023','dd-mm-yyyy')) ,
partition part_q2_2023 values less than(to_date('1-5-2023','dd-mm-yyyy')) ,
partition part_q3_2023 values less than(to_date('1-6-2023','dd-mm-yyyy')));
Query OK, 0 rows affected (0.044 sec)

创建全局唯一索引。

obclient > CREATE UNIQUE INDEX ind_part001 ON part01 (time_id) GLOBAL;
Query OK, 0 rows affected (0.950 sec)

查看索引的状态。

obclient > SELECT a.table_name,b.index_name,b.status FROM dba_tables a, dba_indexes b WHERE a.OWNER = 'SYS' 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   |
+------------+-------------+----------+
| PART01     | IND_PART001 | VALID    |
+------------+-------------+----------+
1 rows in set (0.003 sec)

插入数据。

obclient > insert into part01 (prod_id,cust_id,time_id,quantity_sold) values (1,12,to_date('2023-03-01','yyyy-mm-dd'),103);
sert into part01 (prod_id,cust_id,time_id,quantity_Query OK, 1 row affected (0.001 sec)

obclient > insert into part01 (prod_id,cust_id,time_id,quantity_sold) values (2,12,to_date('2023-04-01','yyyy-mm-dd'),103);
Query OK, 1 row affected (0.001 sec)

obclient > insert into part01 (prod_id,cust_id,time_id,quantity_sold) values (3,12,to_date('2023-05-01','yyyy-mm-dd'),103);
Query OK, 1 row affected (0.000 sec)

提交数据。

obclient [SYS]> commit;
Query OK, 0 rows affected (0.001 sec)

查询表。

obclient [SYS]> select * from part01;
+---------+---------+-----------+---------------+
| PROD_ID | CUST_ID | TIME_ID   | QUANTITY_SOLD |
+---------+---------+-----------+---------------+
|       1 |      12 | 01-MAR-23 |           103 |
|       2 |      12 | 01-APR-23 |           103 |
|       3 |      12 | 01-MAY-23 |           103 |
+---------+---------+-----------+---------------+
3 rows in set (0.002 sec)

查询表上索引状态。

obclient [SYS]> SELECT a.table_name,b.index_name,b.status
    FROM dba_tables a, dba_indexes b
    WHERE a.OWNER = 'SYS'
    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   |
+------------+-------------+----------+
| PART01     | IND_PART001 | VALID    |
+------------+-------------+----------+
1 rows in set (0.002 sec)

查询分区表内容。

obclient [SYS]> select * from part01 partition(part_q1_2023);

输出结果如下:

+---------+---------+-----------+---------------+
| PROD_ID | CUST_ID | TIME_ID   | QUANTITY_SOLD |
+---------+---------+-----------+---------------+
|       1 |      12 | 01-MAR-23 |           103 |
+---------+---------+-----------+---------------+
1 row in set (0.002 sec)

truncate 分区表内容并更新全局索引信息。

obclient [SYS]> ALTER TABLE PART01 TRUNCATE partition part_q1_2023 UPDATE GLOBAL INDEXES;
Query OK, 0 rows affected (1.089 sec)

查询索引状态。

obclient > SELECT a.table_name,b.index_name,b.status
FROM dba_tables a, dba_indexes b
WHERE a.OWNER = 'SYS'
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   |
+------------+-------------+----------+
| PART01     | IND_PART001 | VALID    |
+------------+-------------+----------+
1 rows in set (0.002 sec)

查看表分区 1 内容。

obclient [SYS]> select * from part01 partition(part_q1_2023);
Empty set (0.004 sec)

查询分区表 2 内容。

obclient [SYS]> select * from part01 partition(part_q2_2023);

输出结果如下:

+---------+---------+-----------+---------------+
| PROD_ID | CUST_ID | TIME_ID   | QUANTITY_SOLD |
+---------+---------+-----------+---------------+
|       2 |      12 | 01-APR-23 |           103 |
+---------+---------+-----------+---------------+
1 row in set (0.003 sec)

drop 掉分区表。

obclient [SYS]> ALTER TABLE PART01 drop partition part_q2_2023 UPDATE GLOBAL INDEXES;
Query OK, 0 rows affected (0.970 sec)

查看索引状态。

obclient [SYS]> SELECT a.table_name,b.index_name,b.status
   FROM dba_tables a, dba_indexes b
   WHERE a.OWNER = 'SYS'
   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   |
+------------+-------------+----------+
| PART01     | IND_PART001 | VALID    |
+------------+-------------+----------+
1 rows in set (0.002 sec)

查询分区表 2 内容。

obclient [SYS]> select * from part01 partition(part_q2_2023);
ORA-02149: Specified partition does not exist

查询分区3。

obclient [SYS]> select * from part01 partition(part_q3_2023);

输出结果如下:

+---------+---------+-----------+---------------+
| PROD_ID | CUST_ID | TIME_ID   | QUANTITY_SOLD |
+---------+---------+-----------+---------------+
|       3 |      12 | 01-MAY-23 |           103 |
+---------+---------+-----------+---------------+
1 row in set (0.002 sec)

truncate 分区不更新索引信息。

obclient [SYS]> ALTER TABLE part01 truncate partition part_q3_2023;
Query OK, 0 rows affected (0.051 sec)

查询索引状态。

obclient [SYS]> SELECT a.table_name,b.index_name,b.status
   FROM dba_tables a, dba_indexes b
   WHERE a.OWNER = 'SYS'
   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   |
+------------+-------------+----------+
| PART01     | IND_PART001 | UNUSABLE |
+------------+-------------+----------+
1 rows in set (0.002 sec)

删除失效索引。

obclient [SYS]> DROP INDEX IND_PART001;
Query OK, 0 rows affected (0.022 sec)

重建索引。

obclient [SYS]> CREATE UNIQUE INDEX ind_part001 ON part01 (time_id) GLOBAL;
Query OK, 0 rows affected (0.955 sec)

查询索引状态。

obclient [SYS]> SELECT a.table_name,b.index_name,b.status FROM dba_tables a, dba_indexes b
WHERE a.OWNER = 'SYS'
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   |
+------------+-------------+----------+
| PART01     | IND_PART001 | VALID    |
+------------+-------------+----------+
1 rows in set (0.002 sec)

添加新分区。

obclient [SYS]> ALTER TABLE part01 ADD PARTITION part_q4_2023 VALUES LESS THAN(to_date('1-9-2023','dd-mm-yyyy'));
Query OK, 0 rows affected (0.049 sec)

查看索引状态。

obclient [SYS]> SELECT a.table_name,b.index_name,b.status FROM dba_tables a, dba_indexes b WHERE a.OWNER = 'SYS' 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   |
+------------+-------------+----------+
| PART01     | IND_PART001 | VALID    |
| SALES      | SALE_PK     | UNUSABLE |
| TEST001    | TEST_INDX   | UNUSABLE |
| T_NEW      | T_NEW001    | VALID    |
| T_NEW      | I           | VALID    |
| ZGQTEST    | ZGQ_INDX    | UNUSABLE |
+------------+-------------+----------+
6 rows in set (0.002 sec)

OceanBase 数据库 V2.X 版本不兼容 Oracle 的索引类型及其替代方式

分区表全局索引回表显示 scan 所有 partition 问题

OceanBase 数据库