简介

不可用索引是指由于索引本身问题而无法被所有 SQL 使用的索引。与因 SQL 写法不当而无法使用索引的索引失效情况不同，索引变为不可用时，原本可以使用该索引的 SQL 都将无法使用该索引，只能选择全表扫描或全分区扫描，这将导致 SQL 执行效率大幅下降。如果并发高一些，将会耗尽数据库主机硬件资源，导致所有请求响应超时。

导致索引不可用的常见情况

1. 当用 truncate/drop/exchange 操作分区时，全局索引会失效。增加 updateglobalindexes 参数，全局索引就不会失效了。
2. exchange 的临时表没有索引，或者有索引，没有用 includingindexes 的关键字，会导致局部的索引失效，就是某个分区失效。重建局部索引只能用 alterindexlocal_idxrebuildpartition p1 这样的方式。
3. 分区表 SPLIT 的时候，如果 MAX 区中已经有记录了，这个时候 SPLIT 就会导致有记录的新增分区的局部索引失效！
4. 对表执行 move 操作，将导致表上所有索引变为不可用。
5. sqlldr 如带有 skip_index_mntenance=true 参数，数据导入时将不维护索引，会导致表上所有索引不可用。所以，在完成数据导入后，需重建表上所有索引。

不可用索引的严重性

一旦出现不可用索引，就特别容易引发生产事故。所以，请特别重视，排查原因，消除隐患。

修复建议

1. 找出导致索引不可用的原因

可从 Oracle alert 日志中查找索引不可用的日志，根据时间点排查相关表的维护类操作，进而确定引发的原因。ssh 登陆 Oracle 服务器上，查看日志命令： ```bash cd /home/db/oracle/diag/rdbms/<库名>/<实例名>/trace grep -i -w unusable -B2 -A2 alert_<实例名>.log ```

2. 修复不可用索引

对于全局分区索引，建议将索引删掉后重建。对于本地分区索引，重建不可用索引分区或索引子分区。 参考 SQL 语句： ```sql select from user_indexes where status=UNUSABLE; alter index rebuild online; select from user_ind_partitions where status=UNUSABLE; alter index rebuild partition online; select from user_ind_subpartitions where status=UNUSABLE; alter index rebuild subpartition online; ```

---

Oracle数据库索引优化技术关联查询性能调优

数据库性能优化是无止境的 无论哪种优化技术只是一种手段 但最重要的不是技术 而是思想 掌握了索引优化技术仅仅刚入门 只有融会贯通 举一反三才能成为高手

◆m_patient 患者信息表 我们要用到的字段包括i_patientid（患者ID） s_name（患者姓名） s_code（患者住院号） i_age（患者年龄） i_dept（患者所在病区）

◆lis_code_dept 病区信息表 我们要用到的字段包括i_id（病区ID 主键 与m_patient中的i_dept关联） s_name（病区名）

最终我们构造的SQL如下

select a i_checkno a d_checkdate b s_name b s_code b i_age c s_name from lis_report a inner join m_patient b on a i_patientid = b i_patientid inner join lis_code_dept c on b i_dept = c i_id where a d_checkdate > and a d_checkdate < and b i_age>= and b s_name like 周% order by a d_checkdate desc

我们的SQL使用的这三张表除了创建主键时自动创建的索引外 均未创建其它索引 下图是无索引时的执行计划

表m_patient和lis_report都使用了全表扫描 m_patient全表扫描的成本是 lis_report全表扫描的成本是 只有表lis_code_dept因关联时使用的是其主键 因此这里使用了主键索引 从而避免了全表扫描 它的成本是 我们知道提高查询性能的目标之一就是消灭掉全表扫描 因此我们应该给表m_patient和lis_report加上适当的索引 在SQL代码的where子句中 对m_patient表 我们引用了i_age和s_name字段 对lis_report表 我们引用了d_checkdate字段 通常给这些条件中引用的字段加上索引会提高查询速度 我们先给m_patient的i_gae字段加上索引 下面是对应的执行计划

表m_patient的全表扫描消失了 取而代之的是索引唯一性扫描 成本从 一下子降低到 了 注意这里并未使用我们给i_age增加的索引 但却靠它触发了使用表主键对应的索引 但表lis_report仍然是全表扫描 由于where子句中引用了该表的d_checkdate字段 因此我们给该字段加上索引看看效果

表lis_report的全表扫描消失了 取而代之的是索引范围降序扫描（INDEX RANGE SCAN DESCENDING） 成本也从 下降到 注意这里的索引范围降序扫描的来历 因为我的where子句中引用d_checkdate是介于 至 的一个范围 这时引用的这种字段上建立的索引通常都是执行范围扫描 因为这种条件返回的值往往不止一行 使用降序扫描的原因是order by子句使用了降序排序 如果我们将SQL代码中的 order by a d_checkdate desc 改为 order by a d_checkdate 则变为索引范围扫描（INDEX RANGE SCAN）

至此我们全部消除了全表扫描 我们看到加上索引后 查询执行的成本开销也有所降低 因为数据库表中的记录数不大 因此效果不太明显 如果有上百万条记录则会更直观

lishixinzhi/Article/program/Oracle//

如何彻底解决oracle 索引失效问题

你的脚本是怎么写的？insert插入还会使索引失效？第一次听说。 。 。 。 应该是你脚本写的有问题吧比如删除分区的时候没有update global indexes先把脚本让我看看~~另外，你的分区表上的索引是分区索引local index还是全局索引global index？