如何优化Oracle索引，提升查询速度

1 各类未正确使用索引案例

1.1 统计信息未收集案例
1.1.1 问题复现
–1、在test表中的name4字段上创建索引
create index idx_name4 on test(name4);
–2、按name4字段来进行查询
select * from test where name4>=‘reiku2171160’ and name4 <=‘wazhi9645963’;
–3、查看执行计划

1.1.2 未正确使用索引原因分析
上述查询在字段上创建了索引，使用该字段进行查询，查询执行计划发现未使用该字段的索引，初步分析可能由于统计信息未收集问题导致，通过如下语句查询，如果几列数据为空则代表未收集：
–4、查询表的统计信息是否收集
select num_rows,avg_row_len,last_analyzed from dba_tables where table_name=‘TEST’;

1.1.3 问题解决
经过分析，基本定位由于统计信息未收集，导致未使用索引；通过统计信息收集来解决类似问题，具体操作如下：
–5、表的统计信息收集
begin
dbms_stats.gather_table_stats(ownname=>‘TOMITY’,
tabname=>‘TEST’,
estimate_percent=>100,
method_opt=>‘for all indexed columns’ ,
cascade=>true,
degree=>4);
end;

–6、收集后，查看数据字典中的信息是否已更新
select num_rows,avg_row_len,last_analyzed from dba_tables where table_name=‘TEST’;

–7、查看执行计划
select * from test where name4>=‘reiku2171160’ and name4 <=‘wazhi9645963’;

1.2 索引设计不合理案例
1.2.1 问题复现
–1、在test表中的name4字段上创建索引
create index idx_name4 on test(name4);

–2、在test表中的name5字段上创建索引
create index idx_name5 on test(name5);

–3、查看执行计划
select * from test where name4=‘reiku2171160’ and name5=‘wazhi7487843’

1.2.2 未正确使用索引原因分析
查询条件name4,name5经常大量使用（首先与业务沟通，条件是否经常组合出现），但开发人员在两个字段上分别加上索引，执行时只能被使用到其中一个索引，导致执行效率下降，并且加大索引维护的成本。
1.2.3 问题解决
针对该类问题，应使用组合索引，删除原有的两个索引。具体操作如下：
–4、删除name4,name5上的两个索引
drop index idx_name4;
drop index idx_name5;

–5、创建name4,name5的复合索引
create index idx_name4name5 on test(name4,name5);

–6、查看执行计划
select * from test where name4=‘reiku2171160’ and name5=‘wazhi7487843’

1.3 强制索引案例
1.3.1 问题复现
–1、在test表中的name4字段上创建索引
create index idx_name4 on test(name4);

–2、在test表中的name5字段上创建索引
create index idx_name5 on test(name5);

–3、查看执行计划
select * from test where name4=‘reiku2171160’ and name5=‘wazhi7487843’

1.3.2 未正确使用索引原因分析
表中有两个索引idx_name4，idx_name5，与业务沟通，通过索引idx_name5执行，速度会更快，但由于ORACLE内部规划，使用了错误的索引执行，导致执行速度变慢。
1.3.3 问题解决
通过强制索引的方式，强制使用索引idx_name5,具体方法如下：
–3、强制走索引
select /*+index(t idx_name5) */ * from test t where name4=‘reiku2171160’ and name5=‘wazhi7487843’