统一张表，查询条件的字段一样，为什么效率差那么多?

Question

查询同一张表，该表结构为
create table log (a varchar(10),b date,c varchar(20));其中,a 和 a,b分别为索引
查询语句1：
select * from log where a='A1000' and b>=to_date(...) and b<=to_date(...) order by b；用时2秒。
查询语句2：
select * from log where a='A2314' and b>=to_date(...) and b<=to_date(...) order by b；用时2分多。返回的记录数差不多，都是1000多条。为什么会有这么大的差别？怎么分析这种情况？

Answer 1

原因可能在于log表中符合条件a='A2314'的纪录数太多，系统不使用索引
而a='A1000'的就比较少，使用了索引还是看一下执行计划有什么区别吧。

Answer 2

CBO不一定会选择最优的方法，做好计划贴出来看看

Answer 3

以下是执行计划和TRACE结果，为什么没有用到联合索引idx_ab(a,b)?
语句1：
Execution Plan
----------------------------------------
0 select statement optimizer=choose
1  0 sort<order by>
2  1   table access <by index rowid> of 'log'
3  2     index <range scan> of 'idx_a' <non-unique>
Statistics
-------------------------------------
0 recursive calls
0 db block gets
3125 consistent gets
2638 physical reads
60  redo size
43981 bytes sent via SQL*Net to client
1218 bytes received via SQL*Net from client
67 SQL*Net roundtrips to/from client
1 sorts<memory>
0 sorts<disk>
979 rows processed
语句2：
Execution Plan
----------------------------------------
0 select statement optimizer=choose
1  0 sort<order by>
2  1   table access <by index rowid> of 'log'
3  2     index <range scan> of 'idx_a' <non-unique>Statistics
-------------------------------------
0 recursive calls
0 db block gets
43672 consistent gets
43152 physical reads
52  redo size
73252 bytes sent via SQL*Net to client
1504 bytes received via SQL*Net from client
93 SQL*Net roundtrips to/from client
1 sorts<memory>
0 sorts<disk>
1368 rows processed

Answer 4

我看结果，主要是consistent gets和physical reads有数量级的差别，怎么消除这种差别？

Answer 5

再建一个基于函数to_date()的索引，回快一些。不过差别肯定有！

Answer 6

只需将 ORDER BY b 改成 ORDER BY a, b 即可。原因：
从你的执行计划看来，第二步是“1  0 sort<order by>”，也就是说从这里开始你原本的SQL就没有采用INDEX (a,b)，因此后面的执行也就会变得非常缓慢。

Answer 7

consistent gets 不一致，可能是 a='A2314'  正在被别人修改，而且没有提交。在读取 a='A2314'  的数据的时候，数据库发现这些数据被修改了，而且没有提交，数据库就会去回滚段上找这些数据，开销变大了，自然变慢了。

Answer 8

这个问题应当是没有正确的使用索引。你的两个语句都用上了索引，但是只用到了a字段上的索引idx_a。系统根据idx_a找到符合a条件记录的Rowid后，根据RowId检索到记录，然后在根据记录中的b字段值进行判断，每对b字段的判断一次会读取一次磁盘。a='A2314'的记录数大概是a='A1000'的记录数的十倍，所以physical reads消耗也相差十倍就不足为怪了。重要的是使用上复合索引，把a、b字段判断索引的过程在索引内解决，而不是根据Rowid把b字段先读出来再判断。如果没有分析表，可以运行下列语句后再试试。
dbms_stats.gather_table_stats('用户名','表名');如果还没有使用索引可以使用提示符强制使用索引
select /*+ INDEX(idx_ab)*/ * from log where a='A2314' and b>=to_date(...) and b<=to_date(...) order by b