当索引出现问题时，会导致严重的性能问题，索引问题包括索引不可用、索引碎片导致性能下降，我们需要一些手段在检测索引的问题，并解决这些问题。这一篇将为你讲述怎么定位索引问题，并提供了解决的办法。

索引不可用

索引不可用的原因有很多，包括：
1）索引空间耗尽，导致SQL*Loader更新索引失败；
2）创建索引的过程中实例失败；
3）唯一键有重复值；
4）某个索引的顺序与sorted indexes子句中指定的顺序不同；
5）移动表或表分区（alter table move和alter table move partition）；
6）对表执行在线重定义；
7）截断表分区（alter table truncate partition）；
8）导入分区；
9）删除表分区；
10）拆分表的分区或子分区（alter table split partition）；
11）分区索引的维护操作（alter index split partition）。
除了上述的这些原因之外，你还可以手动的将索引标注为不可用，这样可以使批量加载速度更快，下面是把索引的状态改变为不可用的方法：

alter index IDX_HISTORYALARM_HOUR$01 unusable

如果你的索引为分区索引，这个操作将导致所有分区的索引都不可用，你也可以指定某个分区的索引不可用：

alter index IDX_HISTORYALARM$02 modify partition HISTORYALARM20140731 unusable

通过下面的方法可以查看索引的状态：

select ind.INDEX_NAME,ind.status,ind.PARTITIONED from user_indexes ind where index_name like '%HISTORYALARM%'

INDEX_NAME			STATUS		PARTITIONED
---------------------------------------------------------------------
IDX_HISTORYALARM_HOUR$01		UNUSABLE		NO
IDX_HISTORYALARM$02			N/A		YES

可以看到，全局索引的状态已经变为UNUSABLE，但本地索引的状态标识为N/A，通过下面的方法可以查看本地索引在每一个分区中的索引状态：

INDEX_NAME			PARTITION_NAME			STATUS
------------------------------------------------------------------------------------------
IDX_HISTORYALARM$02			HISTORYALARM20140731		UNUSABLE
IDX_HISTORYALARM$02			HISTORYALARM20140801		USABLE
IDX_HISTORYALARM$02			HISTORYALARM20140802		USABLE
IDX_HISTORYALARM$02			HISTORYALARM20140803		USABLE
IDX_HISTORYALARM$02			HISTORYALARM20140804		USABLE
IDX_HISTORYALARM$02			HISTORYALARM20140805		USABLE
......

可以看到分区HISTORYALARM20140731的索引已经标注为UNUSABLE。
当索引被标注为不可用后，优化器就会忽略这些索引，Oracle在DML更改表时也不再维护这些索引，如果希望优化器再次使用它，就必须先重建（rebuild）索引。

索引碎片

随着时间的推移，由于大量的删除操作，索引可能会产生碎片。Oracle文档（Performance Tuning Manual for Oracle DataBase 11.2）建议运行"analyze...validate"语句来识别需要重建的索引，这个操作会将索引的统计数据放到INDEX_STATS视图中，下面是该视图中的关键列：
1）高度（HEIGHT）：索引的高度，从1开始，1代表只有根的索引；
2）块数（BLOCKS）：分配给索引的块数；
3）叶行数（LF_ROWS）：叶行数（包括已删除的行）；
4）已删除的叶行数（DEL_LF_ROWS）：已删除尚未清理的叶行条目数；
5）已用空间（USED_SPACE）：索引内使用的总空间（包括已删除的条目）；
6）已用百分比（PCT_USED）：索引内使用空间的百分比（包括已删除的条目）。。计算公式：(USED_SPACE / BTREE_SPACE) * 100;
7）B树空间（BTREE_SPACE）：索引的总大小（包括已删除的条目）。
下面通过一个例子来学习该视图的使用。
先创建一个测试表格，并在上面创建索引：

create table test as select rownum id,'Test' text from dual connect by level <= 100000;
create index idx_test on test(id);

然后执行索引分析语句：

analyze index idx_test validate structure;

注意在执行分析语句之前INDEX_STATS视图是空的，现在查询该视图来检查被删除的叶行数：

select lf_rows,lf_blks,del_lf_rows from index_stats;

LF_ROWS		LF_BLKS		DEL_LF_ROWS
--------------------------------------------------------------------------------
100000		222		0

这里可以看出删除的叶行数为0，接下来我们删除表中大量的行，再次运行分析语句：

delete test where id <= 99999;
commit;
analyze index idx_test validate structure;

然后查询被删除的叶行数：

select lf_rows,lf_blks,del_lf_rows from index_stats;

LF_ROWS		LF_BLKS		DEL_LF_ROWS
-----------------------------------------------------------
100000		222		99999

为了让Oracle能够得到正确的执行计划，我们先收集表和索引的统计信息：

begin
  dbms_stats.gather_table_stats(ownname   => user,
                                tabname => 'TEST',
                                cascade   => TRUE);
end;

然后执行一个索引范围扫描的查询：

select * from test where id > 10;

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.27          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      0.01       1.53         56        224          0           1
------- ------  -------- ---------- ---------- ---------- ---

Oracle性能分析9：重建索引(一)

索引不可用

索引碎片