oracle 跑批 删除索引,Oracle删除索引规范

1.背景概述

近期应用升级上线过程中，存在删除业务表索引的变更操作，且因删除索引导致次日业务高峰时期，数据库响应缓慢的情况，经定位是缺失索引导致。与用户沟通，虽然变更中删除索引的需求很少，但也存在此类需求。

本文从数据库层面，旨在尽可能避免类似问题发生，制定删除索引的变更规范。

2.索引删除规范

若确认需要做索引删除，可以使用Oracle提供的两个功能特性协助判断删除索引是否会有隐患。

2.1 增加索引监控

将计划要删除的索引经过至少一个业务周期(具体业务确认业务周期为多久，注意要考虑到跑批场景)的监控，如果整个业务周期，该索引一直没有被使用过则可以考虑删除。

演示案例：

create table T as select * from dba_objects;

create index IDX_T_01 on T(object_id);

假设要删除的索引名称是IDX_T_01，使用下面语句开启该索引的监控。

alter index jingyu.IDX_T_01 monitoring usage;

索引是否使用到，会在具体业务schema下的v$object_usage视图中体现(具体观察USED这一列的值，如果是NO，说明自监控以来该索引从未使用过)

conn jingyu/jingyu

col index_name for a30

col table_name for a30

col START_MONITORING for a30

col END_MONITORING for a30

set lines 180

select * from v$object_usage;

INDEX_NAME TABLE_NAME MONITO USED START_MONITORING END_MONITORING

---------- ---------- ------ ------ ------------------------------ ------------------------------

IDX_T_01 T YES NO 07/22/2020 14:15:18

如果有人/应用执行过用到该索引的语句，比如：

select object_id from t where object_id = 3;

此时再观察USED这一列的值，已经变为yes，说明自监控以来该索引有被使用过，就不能被轻易删除：

INDEX_NAME TABLE_NAME MONITO USED START_MONITORING END_MONITORING

---------- ---------- ------ ------ ------------------------------ ------------------------------

IDX_T_01 T YES YES 07/22/2020 14:15:18

如果不再需要监控该索引，可以这样取消该索引的监控：

alter index jingyu.IDX_T_01 nomonitoring usage;

INDEX_NAME TABLE_NAME MONITO USED START_MONITORING END_MONITORING

---------- ---------- ------ ------ ------------------------------ ------------------------------

IDX_T_01 T NO NO 07/22/2020 14:30:30 07/22/2020 14:30:58

优点：简单，能有效监控整个业务周期内索引是否被使用到，如果没有被使用则可以放心删除。

缺点：只能判断是否被使用到，不能判断索引使用频率。

2.2 将删除索引先修改为不可见

将计划要删除的索引设置为不可见(invisible)，然后经历至少一个业务周期(具体业务确认业务周期为多久，注意要考虑到跑批场景)的观察，确认没有影响，则可以考虑彻底删除。

设置索引IDX_T_01不可见：

alter index jingyu.IDX_T_01 invisible;

执行演示SQL发现已经是全表扫：

explain plan for select object_id from t where object_id = 3;

select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT

----------------------------------------------------------------------------------------------------

Plan hash value: 1601196873

--------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

--------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 11 | 143 | 283 (2)| 00:00:04 |

|* 1 | TABLE ACCESS FULL| T | 11 | 143 | 283 (2)| 00:00:04 |

--------------------------------------------------------------------------

恢复索引IDX_T_01可见：

alter index jingyu.IDX_T_01 visible;

执行演示SQL发现又恢复了索引访问，无需重建：

explain plan for select object_id from t where object_id = 3;

select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT

----------------------------------------------------------------------------------------------------

Plan hash value: 2968633466

-----------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

-----------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 13 | 1 (0)| 00:00:01 |

|* 1 | INDEX RANGE SCAN| IDX_T_01 | 1 | 13 | 1 (0)| 00:00:01 |

-----------------------------------------------------------------------------

优点：因为invisible索引只是让优化器不可见，索引段中的数据依然存在且DML操作也会维护这些invisible的索引，所以回退(直接修改该索引为可见)非常方便。

缺点：如果删除索引是为了更快加载数据，那么设置索引invisible期间，并不会提升效率。另外应用会话如果有设置OPTIMIZER_USE_INVISIBLE_INDEXES=TRUE的参数，也会用到invisible索引，而这可能会造成误判，需要特别注意。

3.根本解决方案及建议

删除索引的情景一般是考虑到索引数量过多，从而导致索引维护成本和空间使用成本增加。一般原则是首先评估删除冗余索引，比如某张表同时有两个索引，索引A是c1列，索引B是c1，c2两列的复合索引，则一般可以选择删除索引A；但需要注意，如果索引B是c2和c1列的复合索引，就通常不可以删除索引A。其次，对其他计划删除的索引可以按照上文的规范来评估和操作。