个查询的
where 条件(where clause)包含一个谓词(predicate,其实就是一个判断条件,
如
”=”
,
“>”
,
”<”等),而且该谓词上引用的列上有有效索引,那么优化器将使用索引访
问这个表,而不考虑其它因素,如表中数据的多少、表中数据的易变性、索引的可选择性等。
此时数据库中没有关于表与索引数据的统计性描述,如表中有多上行,每行的可选择性等。
优化器也不考虑实例参数,如
multi block i/o、可用排序内存的大小等,所以优化器有时就
选择了次优化的计划作为真正的执行计划,导致系统性能不高。
如,对于
select * from emp where deptno = 10;
这个查询来说,如果是使用基于规则的优化器,而且
deptno 列上有有效的索引,则会
通过
deptno 列上的索引来访问 emp 表。在绝大多数情况下,这是比较高效的,但是在一些
特殊情况下,使用索引访问也有比较低效的时候,现举例说明:
1
)
emp 表比较小,该表的数据只存放在几个数据块中。此时使用全表扫描比使用索引
访问
emp 表反而要好。因为表比较小,极有可能数据全在内存中,所以此时做全表扫描是
最快的。而如果使用索引扫描,需要先从索引中找到符合条件记录的
rowid,然后再一一根
据这些
rowid 从 emp 中将数据取出来,在这种条件下,效率就会比全表扫描的效率要差一
些。
2
)
emp 表比较大时,而且 deptno = 10 条件能查询出表中大部分的数据如(50%)。如
该表共有
4000 万行数据,共放在有 500000 个数据块中,每个数据块为 8k,则该表共有约
4G,则这么多的数据不可能全放在内存中,绝大多数需要放在 硬盘上。此时如果该查询通
过索引查询,则是你梦魇的开始。
db_file_multiblock_read_count 参数的值 200。如果采用全表
扫描,则需要
500000/db_file_multiblock_read_count=500000/200=2500 次 I/O。但是如果采用
索引扫描,假设
deptno 列上的索引都已经 cache 到内存中,所以可以将访问索引的开销忽
略不计。因为要读出
4000 万 x 50% = 2000 万数据,假设在读这 2000 万数据时,有 99.9%的
命中率,则还是需要
20000 次 I/O,比上面的全表扫描需要的 2500 次多多了,所以在这种情
况下,用索引扫描反而性能会差很多。在这样的情况下,用全表扫描的时间是固定的,但是
用索引扫描的时间会随着选出数据的增多使查询时间相应的延长。
上面是枯燥的假设数据,现在以具体的实例给予验证:
环境:
oracle 817 + Linux + 阵列柜,表 SWD_BILLDETAIL 有 3200 多万数据;
表的
id 列、cn 列上都有索引
经查看执行计划,发现执行
select count(id
)
from SWD_BILLDETAIL;使用全表扫描,