删除数据库表分区后，索引不可用（失效）的解决方案

原创微信公众号：数人之道 2022-01-08

9955

本文转载自微信公众号：数人之道

三个月前的一次生产环境数据库操作事故，至今仍然历历在目、难以忘怀。这次血与泪的教训需要被记录下来，鉴前毖后。这就是这篇迟来的教训总结的成文背景。

1. 业务背景

由于业务系统中的常用表存储的数据量过大（6.5 亿条记录，合共 4.8TB 大小），使用 Oracle 数据库查询效率较低，对业务的使用造成了一定的影响。与业务人员及系统厂商商议后，决定在系统生产环境中进行 Oracle 的 housekeep（数据清理）操作，将相关业务表当前时点 90 天以前的数据删除，并将其数据的生命周期配置为 90 天（即只保留 90 天的数据）。

2. 问题描述

由于数据量较大，因此在删除之前先对标的表创建了分区，再进行以下步骤的操作：

清空时间戳在 90 天以前的表分区数据：truncate partition
删除被清空的分区：drop partition
删除尚存表分区中时间戳在 90 天以前的数据：delete
创建过渡表用于删除若干张表的整表数据：create
以重命名的方式删除若干张表的整表数据（将若干张原表重命名，再将过渡表重命名为原表名）：rename
在系统后端配置表数据的生命周期

在执行第 1、2 步操作，删除分区的时候没有遇到任何问题，但在第 3 步操作时 DBA 反馈执行报错，报错信息为：

ORA-01502: index ‘xxx.xxx’ or partition of such index is in unusable state

同时，业务人员反映有客户遇到查询速度十分慢，甚至无法查询出信息结果、上传文件会报错的问题。

3. 原因分析

根据问题表象，需要从索引状态和索引唯一性两方面对问题的根因进行查证分析。

3.1. 查询报错索引的状态

先执行以下 SQL 语句查询报错索引相应的索引状态：

SELECT table_owner
     , table_name
     , index_name
     , uniqueness
     , tablespace_name
     , status
  FROM all_indexes
 WHERE index_name IN ('xxx','yyy','zzz')
;

查询结果发现 status 字段的值全部为 ‘UNUSABLE’，即所有索引的状态均为不可用。另外，查询结果中，uniqueness 字段的部分值为 ‘UNIQUE’，说明存在唯一性索引。

3.2. 验证索引的唯一性约束

执行以下 SQL 语句验证索引的唯一性约束：

SELECT t1.owner
     , t1.constraint_type
     , t1.table_name
     , t2.column_name
  FROM all_constraints t1
  LEFT JOIN all_cons_columns t2
    ON t1.table_name = t2.table_name
   AND t1.constraint_name = t2.constraint_name
  LEFT JOIN all_indexes t3
    ON t1.table_name = t3.table_name
   AND t1.constraint_name = t3.index_name
 WHERE t1.constraint_name IN ('xxx','yyy','zzz')
   AND t3.uniqueness='UNIQUE'
;

查询发现唯一性索引的 constraint_type 均为 ‘P’，即主键约束，而主键约束所在的列会自动创建唯一性索引，双向验证了索引的唯一性约束。另外，在查询结果中还发现每个涉及失效索引的表均存在唯一性索引。

3.3. 问题原因查证

关于第一个索引状态的问题，经过查证，是由于删除存在数据的分区，导致分区表上的全局索引（建立分区时没有指定，则默认为全局索引）变成不可用的失效状态。正是由于索引变成不可用，查询数据的时候需要进行全表扫描，导致查询的效率十分低下甚至超时。而此问题没有在演练环境被识别出来的原因是，演练环境中被删除的分区不存在数据，是不会导致索引不可用的（这个问题下面会有详细解释），也正因此，演练环境在数据上也需要保证跟生产的一致（针对演练环境的数据问题，我们后面也进行了整改，这是后话）。

关于第二个唯一性索引的问题，经过查证，对于唯一性索引，如果索引处于不可用的状态，在进行 DML 操作时，就会触发 ORA-01502 错误。

4. 解决方案

经过根因分析，提出 3 套方案应对索引不可用及无法进行 DML 操作的问题。

4.1. 方案一：删除唯一性索引

与业务人员进行沟通，确认唯一性索引是否可以删除而不影响业务，若可行，可以直接删除索引：

DROP INDEX [schema.]<index_name>;

但对于由主键约束或唯一性约束而自动创建的唯一性索引是无法直接被删除的，需要先把相应的约束删除后才能删除索引：

ALTER TABLE [schema.]<table_name> DROP CONSTRAINT <constraint_name>;
DROP INDEX [schema.]<index_name>;

方案可行性分析：此方案简单粗暴，并能快速解决无法进行 DML 操作的问题，但是却忽略了查询效率的问题，若将索引删除，查询依然会很慢方案可行性分析：此方案简单粗暴，并能快速解决无法进行 DML 操作的问题，但是却忽略了查询效率的问题，若将索引删除，查询依然会很慢甚至超时，业务上是无法通过的，因此只能舍弃。

4.2. 方案二：创建局部分区索引

先将原先的索引删除，再针对原索引的键创建相应的局部分区索引：

CREATE INDEX <index_name> ON [schema.]<table_name>(col1,col2,...) LOCAL;

对于主键或唯一性约束，可以使用以下语句创建唯一性局部分区索引：

ALTER TABLE [schema.]<table_name> ADD CONSTRAINT <constarint_name> [PRIMARY KEY | UNIQUE](col1,col2,...)
USING INDEX LOCAL TABLESPACE <tablespace_name>;

方案可行性分析：此方案是最根本的解决方案，创建局部分区索引后，再进行删除分区的操作就不会导致索引变为不可用状态，同时，创建索引后即可解决眼前的查询及 DML 操作问题。但由于以下原因，这个方案也被舍弃：

需要时间编写删除及创建局部分区索引语句，还需要对唯一性索引及非唯一性索引进行识别，删除再创建索引耗时也较长，不适用于目前需要解决燃眉之急的场景。
局部索引的查询速度不及全局索引，不太适合用于 OLTP 系统（下面会有详细分析）。

4.3. 方案三：重建索引

直接对不可用状态的全部索引进行重建：

ALTER INDEX [schema.]<index_name> REBUILD [ONLINE];

方案可行性分析：此方案的缺点是在下一次对分区表进行分区删除后，索引状态又会变为不可用，需要添加相应语句重建索引。但由于其执行难度低，能快速实施，且重建索引后即可解决查询和无法进行 DML 操作的问题，因此最后被采纳。

我们采用 rebuild online 的方式进行索引重建，此方式不需重新构建新的索引，直接执行表扫描获取数据，且不会阻塞 DML 操作，但由于耗时较长，数据的操作会对重建有所影响，因此还是需要停止应用服务进行操作。

5. 知识及经验总结

经过以上一番折腾，能明显感受到学艺不精带来的教训是多么惨痛，真是“啊，多么痛的领悟~”，因此需要给自己补上一课。

注意，下面的知识以 Oracle 为基础。

5.1. 全局索引与局部索引的特点

5.1.1. 全局（global）索引的特点

全局索引分为全局非分区索引和全局分区索引。创建索引时若不进行显式指定，则默认创建的是全局非分区索引；创建全局索引时若显式指定分区子句，则创建的是全局分区索引。
全局索引查询较快，多应用于 OLTP 系统中。
全局索引不便于管理，Oracle 不会自动维护全局索引，对表做分区 DDL 操作会导致其不可用。
全局分区索引必须是前缀索引，全局索引的索引列必须是以索引分区键作为其前几列，即索引列必须是分区键。
全局分区索引只能按范围或者散列分区。
全局索引支持 B+树索引。

5.1.2. 局部（local）索引的特点

局部索引一定也只能是局部分区索引，非分区表无法创建局部索引。
局部索引查询较慢，多应用于数仓环境及 OLAP 系统中。
局部索引具有更高的可用性，更便于管理，部分分区 DDL 操作不会导致其不可用。
局部索引可用是前缀索引，也可以是非前缀索引。
局部索引只支持分区内的唯一性，无法支持表上的唯一性，因此如果要用局部索引做唯一性约束，则约束中必须要包括分区键列。
局部索引支持 B+树索引及位图索引，且位图索引也必须是局部分区索引。

5.2. 全局索引及局部索引的创建语法

5.2.1. 全局（global）索引的创建语法

5.2.1.1. 全局非分区索引：

CREATE INDEX <index_name> ON [schema.]<table_name>(col1);

5.2.1.2. 全局分区索引：

CREATE INDEX <index_name> ON [schema.]<table_name>(col1)  
GLOBAL PARTITION BY RANGE (col1)
(PARTITION <partition_name1> VALUES LESS THAN (1000),
 PARTITION <partition_name2> VALUES LESS THAN (5000),
 PARTITION <partition_name3> VALUES LESS THAN (<max_value>),
);

5.2.2. 局部（local）索引的创建语法

CREATE INDEX <index_name> ON [schema.]<table_name>(col1) LOCAL;

直接在普通索引创建语句后面加上 LOCAL 关键字。

5.3. 分区表索引失效的情况

除了手动将索引置为无效外，分区表的索引在一些分区 DDL 操作后也会变成不可用的失效状态。那么哪些操作会让分区表的索引失效，哪些操作不会呢？下面以表格形式总结分区表的索引是否会失效的情况：

图1：分区表索引是否会失效情况表

在进行分区 DDL 操作的命令后加上UPDATE GLOBAL INDEXES语句，即可避免全局索引失效：

ALTER TABLE [schema.]<table_name> TRUNCATE PARTITION <partition_name> UPDATE GLOBAL INDEXES;

从图1的表中可以看到，在目标分区没有数据的情况下，无论是全局索引还是局部索引，分区 DDL 操作都基本不会导致其失效。这也就解释了为什么在演练环境中进行投产演练没有出现索引不可用的情况。

5.4. 索引失效对 DML 操作的影响

在上面的案例中，我们删除分区导致索引失效后，是无法正常进行 DML 操作的。那么是否所有 DML 操作都无法进行了呢，我们来看看下面索引失效对 DML 操作影响的总结：

图2：索引失效对 DML 操作的影响情况表

可见，索引失效后，除了更新操作，插入和删除均无法正常进行，而对主键进行更新也是会失败的。

5.5. 避免索引失效的经验

最后，我用两句话简单总结一下避免索引出现不可用的失效状态的经验：

针对全局索引，建议在任何分区 DDL 操作命令后都要加上重建索引的语句；针对局部索引，建议在进行除了 truncate 和 drop 的分区 DDL 操作后，都做一次重建索引的操作。

数据库分区索引

最后修改时间：2022-01-08 23:26:35

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者