这个sql为什么用不上索引？

Question

我的表很简单：table X 中有一个varchar(6)的字段month，在table X 的month字段上加了一个normal类型的索引
但我在查询如下sql时，确用不到这个索引，依然是全表扫描：
select *
from X
Where X.month>='200701'
强制使用这个索引时，耗时比不使用索引还要长，oracle自动采用最优方案（不使用这个索引）。
我的问题是为什么采用索引比不使用索引还用忙，我这个sql有没有什么可以优化的地方？

Answer 1

首先楼主要改变一下观念, 不是什么时候用索引都比不用索引快的。你的例子充分说明了这一点.
不用索引的根本原因是ORACLE在你给定的环境下(比如统计资料收集, 初始化参数设置), 认为全表扫描比索引扫描更快,所需执行的IO次数要少. 其次, 你需要了解全表扫描用的是SCATTER READ,就是批量读取,一次读取的块数为DB_MULTIBLOCK_READ_COUNT决定,好象是16个BLOCK, 而索引扫描(你的案例肯定是INDEX RANGE SCAN), 是一个索引块一个索引块的读.如果涉及的记录占的比重多,效率远没有把整表都读到内存快. 这就是为什么B-TREE索引需要访问记录是全表记录一个恰当小的比例(例如小于5%)的原因. 最后注意这个百分比没有定数, 不同的情况它的取值可大可小. 有时候即使是1%, 也不如用全表扫描. 这还得看这个索引的存储效率. 建议: 及时收集表及索引上的统计资料(dbms_stats包), 相信ORACLE的优化器, 去掉HINT. 就你给出的SQL,应该没什么好优化的. 不过记住千万不要忘了200701的引号.

Answer 2

所有的一切你都可以从执行计划中找到答案. 索引在什么时候使用, 什么时候能用上, 影响的因素有几个: 1.表的大小,如果表的记录数不是很多的话,Oracle在解析时发现使用索引时比全表扫描成本还高,当然使用全表扫描. 2.是否分析过记录信息, alter table table_name compute statistics; 如果一个表比较大,而又没有及时地对其进行过分析, 那可能会导致执行计划误差较大, 从而使用全表扫描. 3.索引建的是否合理, 比如,如果已经有了联合索引, 再使用时对哪个字段进行限制条件会使用不同的索引. 4.数据库的初始化参数, 执行计划中计算成本的时候, 调整不同的参数值可能会导致执行计划的结果不同. 比如优化模式:optimizer_mode, optimizer_index_cost_adj .查询的时候, 条件中的字段类型和给定的条件类型不一致时也可能导致索引用不上.