Oracle删除索引规范 |
您所在的位置:网站首页 › oracle删除索引命令 › Oracle删除索引规范 |
1.背景概述
2.索引删除规范
3.根本解决方案及建议
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。其次,对其他计划删除的索引可以按照上文的规范来评估和操作。 |
今日新闻 |
推荐新闻 |
CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |